InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 21:55:50 GMT20http://www.sqlservercentral.com/Forums/Topic1391472-392-1.aspxYou're welcome. Glad that worked for you. :-DMon, 03 Dec 2012 08:33:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1391472-392-1.aspxThank you both. ROW_NUMBER ( ) OVER is what I needed.Mon, 03 Dec 2012 08:23:23 GMTChrissy321http://www.sqlservercentral.com/Forums/Topic1391472-392-1.aspxWith 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.[code]declare @Top intset @Top = 2;with cteCount as( select TestGroup, TestData, ROW_NUMBER() over(partition by TestGroup order by TestData) as RowNum from TestTable)select * from cteCountwhere RowNum <= @Top[/code]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'.[code]INSERT [dbo].[TestTable] (TestGroup,TestData) VALUES ('C',5)[/code]It will find and return only the 1 row for that group.Fri, 30 Nov 2012 12:20:31 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1391472-392-1.aspxHave 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.[url]http://msdn.microsoft.com/en-us/library/ms186734.aspx[/url] ROW_NUMBER()[url]http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx[/url] Common Table Expressions.Also great sample data and ddl.Fri, 30 Nov 2012 11:20:27 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1391472-392-1.aspxHello All,I'd like to specify a dynamic Top criteria and return the top number of records per group.My desired results is:A 1A 2A 3B 1B 2B 3not A 1A 2A 3Thanks 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 returnDECLARE @SelectType varchar(6) --Either Top, Bottom or ALLSET @Top = 3SELECTTOP (@Top) TestGroup, TestDataFROM [dbo].[TestTable]ORDER BY TestDataFri, 30 Nov 2012 11:11:13 GMTChrissy321