Dynamic top per group

Question

Dynamic top per group

Chrissy321

SSCoach

Points: 15763
More actions
November 30, 2012 at 11:11 am

#265595

Hello All,
I'd like to specify a dynamic Top criteria and return the top number of records per group.
My desired results is:
A1
A2
A3
B1
B2
B3
not
A1
A2
A3
Thanks if you can help.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable] (TestGroup char(1),TestData int)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('A',1)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('A',2)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('A',3)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('A',4)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('A',5)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('B',1)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('B',1)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('B',1)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('B',1)
INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('B',1)
DECLARE @Top int --The number of records to return
DECLARE @SelectType varchar(6) --Either Top, Bottom or ALL
SET @Top = 3
SELECT
TOP (@Top)
TestGroup,
TestData
FROM [dbo].[TestTable]
ORDER BY
TestData

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

Have a look at ROW_NUMBER() OVER() and Common Table Expressions. These will allow you to accomplish what you need to do. If you need any help let us know and show us what you have.

http://msdn.microsoft.com/en-us/library/ms186734.aspx ROW_NUMBER()

http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx Common Table Expressions.

Also great sample data and ddl.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

With such a great job posting ddl and sample data I think you deserve a complete answer. I wish more people would make their posts so easy to work with.

declare @Top int

set @Top = 2

;with cteCount as

(

select TestGroup, TestData, ROW_NUMBER() over(partition by TestGroup order by TestData) as RowNum

from TestTable

)

select * from cteCount

where RowNum <= @Top

Now all you have to do is change the value of @Top and the number of rows per group will change. It will also work if @Top specified finds fewer rows for any given group. Say you added a group 'C'.

INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('C',5)

It will find and return only the 1 row for that group.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chrissy321 SSCoach Points: 15763 More actions · Answer 3

Thank you both. ROW_NUMBER ( ) OVER is what I needed.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You're welcome. Glad that worked for you. 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/