Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Dynamic top per group
Add to briefcase

Dynamic top per group Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Chrissy321
Posted Friday, November 30, 2012 11:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:01 PM
Points: 550, Visits: 2,978
Hello All,

I'd like to specify a dynamic Top criteria and return the top number of records per group.

My desired results is:

A 1
A 2
A 3
B 1
B 2
B 3

not

A 1
A 2
A 3

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
Post #1391472
CapnHector
Posted Friday, November 30, 2012 11:20 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 10:07 AM
Points: 935, Visits: 1,709
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 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

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

Jeremy Oursler
Post #1391476
Sean Lange
Posted Friday, November 30, 2012 12:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1391493
Chrissy321
Posted Monday, December 03, 2012 8:23 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:01 PM
Points: 550, Visits: 2,978
Thank you both. ROW_NUMBER ( ) OVER is what I needed.
Post #1391974
Sean Lange
Posted Monday, December 03, 2012 8:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1391983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.