Getting 1st row of a group by

Transact-SQL
1

Hi,
I have got a table where I need to get the 1st row key for each group order by a table's field

Using the sample shown below I am expecting
For Gd_G_Id 2 getting Gd_Id 1 and For Gd_G_Id 3 I want 6

I tried this

WITH tblGaleria_Detalles AS (
    SELECT p.Gd_Id, 
           ROW_NUMBER() OVER(PARTITION BY p.Gd_G_Id 
                             ORDER BY p.Gd_Orden DESC) AS rk
      FROM tblGaleria_Detalles p)
SELECT s.*
  FROM tblGaleria_Detalles s
 WHERE s.rk = 1

But this gives me an error message " Recursive common table expression 'tblGaleria_Detalles' does not contain a top-level UNION ALL operator"

Here is my table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblGaleria_Detalles](
    [Gd_Id] [int] IDENTITY(1,1) NOT NULL,
    [Gd_G_Id] [int] NOT NULL,
    [Gd_P_Id] [int] NULL,
    [Gd_Texto] [nvarchar](250) NULL,
    [Gd_Imagen] [nvarchar](250) NOT NULL,
    [Gd_Orden] [int] NULL,
    [Gd_Estado] [int] NULL,
 CONSTRAINT [PK_tblGaleria_Detalles] PRIMARY KEY CLUSTERED 
(
    [Gd_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[tblGaleria_Detalles] ON 

GO
INSERT [dbo].[tblGaleria_Detalles] ([Gd_Id], [Gd_G_Id], [Gd_P_Id], [Gd_Texto], [Gd_Imagen], [Gd_Orden], [Gd_Estado]) VALUES (1, 2, 0, N'Text 1', N'Img_2_088125700390.jpg', 100, 1)
GO
INSERT [dbo].[tblGaleria_Detalles] ([Gd_Id], [Gd_G_Id], [Gd_P_Id], [Gd_Texto], [Gd_Imagen], [Gd_Orden], [Gd_Estado]) VALUES (2, 3, 0, N'Text 2', N'Img_2_088125703524.jpg', 400, 1)
GO
INSERT [dbo].[tblGaleria_Detalles] ([Gd_Id], [Gd_G_Id], [Gd_P_Id], [Gd_Texto], [Gd_Imagen], [Gd_Orden], [Gd_Estado]) VALUES (3, 2, 0, N'Text 3', N'Img_2_088125705710.jpg', 200, 1)
GO
INSERT [dbo].[tblGaleria_Detalles] ([Gd_Id], [Gd_G_Id], [Gd_P_Id], [Gd_Texto], [Gd_Imagen], [Gd_Orden], [Gd_Estado]) VALUES (4, 2, 0, N'Text 4', N'Img_2_088125708002.jpg', 300, 1)
GO
INSERT [dbo].[tblGaleria_Detalles] ([Gd_Id], [Gd_G_Id], [Gd_P_Id], [Gd_Texto], [Gd_Imagen], [Gd_Orden], [Gd_Estado]) VALUES (6, 3, 0, N'Text 5', N'Img_222.jpg', 200, 1)
GO
SET IDENTITY_INSERT [dbo].[tblGaleria_Detalles] OFF
GO
ALTER TABLE [dbo].[tblGaleria_Detalles] ADD  CONSTRAINT [DF_tblGaleria_Detalles_Gd_Orden]  DEFAULT ((0)) FOR [Gd_Orden]
GO
ALTER TABLE [dbo].[tblGaleria_Detalles] ADD  CONSTRAINT [DF_tblGaleria_Detalles_Gd_Estado]  DEFAULT ((0)) FOR [Gd_Estado]
GO
2

The select inside the with statement, should refer your table, like this:

WITH tblGaleria_Detalles
  AS (SELECT p.Gd_Id
            ,ROW_NUMBER() OVER(PARTITION BY p.Gd_G_Id 
                               ORDER BY p.Gd_Orden DESC
                              ) AS rk
        FROM dbo.tblGaleria_Detalles AS p
     )
SELECT s.*
  FROM tblGaleria_Detalles AS s
 WHERE s.rk = 1
;
1 Like
3

Working fine, thanks !