Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic top per group Expand / Collapse
Author
Message
Posted Friday, November 30, 2012 11:11 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 653, Visits: 3,906
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
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: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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
Posted Friday, November 30, 2012 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1391493
Posted Monday, December 3, 2012 8:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 12:29 PM
Points: 653, Visits: 3,906
Thank you both. ROW_NUMBER ( ) OVER is what I needed.
Post #1391974
Posted Monday, December 3, 2012 8:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:56 PM
Points: 13,081, Visits: 12,545
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1391983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse