September 11, 2023 at 11:23 am
I have a table which contains groups of records , relating to a column called [ChannelID] defined as follows:
DROP TABLE IF EXISTS [#AggDataCheck]
GO
SET NOCOUNT ON
CREATE TABLE [#AggDataCheck](
[level] [tinyint] NULL,
[recordcount] [int] NULL,
[serialnumber] [nvarchar](255) NOT NULL,
[channelid] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (1, 21187, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (2, 10744, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (3, 5523, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (4, 2913, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (5, 1608, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (6, 955, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (7, 628, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (8, 466, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (9, 385, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (10, 344, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (11, 323, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (12, 313, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (13, 308, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (14, 306, N'940860', 11)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (1, 883, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (2, 449, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (3, 232, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (4, 124, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (5, 70, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (6, 43, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (7, 29, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (8, 23, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (9, 20, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (10, 18, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (11, 17, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (12, 16, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (13, 16, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (14, 16, N'940860', 15)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (1, 21187, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (2, 10744, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (3, 5523, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (4, 2913, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (5, 1608, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (6, 955, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (7, 628, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (8, 466, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (9, 385, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (10, 344, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (11, 323, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (12, 313, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (13, 308, N'940860', 14)
GO
INSERT [#AggDataCheck] ([level], [recordcount], [serialnumber], [channelid]) VALUES (14, 306, N'940860', 14)
GO
SET NOCOUNT OFF
Now I want to be able to select the top 1 record out of each group , which satisfies
the following criteria:
Recordcount < 512
However I get only 1 record out of the whole table, where what I really want is
1 top record per channelID
However running this :
SELECT TOP 1 [Level]
FROM #AggDataCheck
WHERE [RecordCount] <=512
ORDER BY [Level]
Gives me only the following:
Gives me only the following:
Level Recordcount SerialNumber ChannelID
8 466 940860 11
When i actually want the following:
Level Recordcount SerialNumber ChannelID
8 466 940860 11
8 466 940860 14
2 449 940860 15
how can I acheive this?
I did think of using partioning or dense rank but not sure ..
September 11, 2023 at 11:36 am
Like this?
WITH ordered
AS (SELECT *
,rn = ROW_NUMBER () OVER (PARTITION BY adc.channelid ORDER BY adc.recordcount DESC)
FROM #AggDataCheck adc
WHERE adc.recordcount <= 512)
SELECT o.level
,o.recordcount
,o.serialnumber
,o.channelid
FROM ordered o
WHERE o.rn = 1;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 11, 2023 at 2:26 pm
Hi Phil
That works for me.. thank you very much for that
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply