TOP clause on a GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TOP clause on a GROUP BY

Author

Topic

mjda
Starting Member

6 Posts

Posted - 2007-04-20 : 03:42:08

Hi all,
Is there a way to return just the TOP x results for each group of a group by query in a single statement? Something like this:

SELECT TOP 5 * FROM myTable ORDER BY orderingField GROUP BY groupId

Meaning: get 5 records for each group.

best regards,
Manuel Alves

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-20 : 04:18:42

[code]create table TBl1(SalesPersonID int,OrderID varchar(20))

insert Into TBl1(SalesPersonID,OrderID)
select 1,'21321adfss321'
Union All
select 1,'21321fads321'
Union All
select 1,'2132adf1321'
Union All
select 1,'213213adf21'
Union All
select 1,'21321adf321'
Union All
select 1,'213213afd21'
Union All
select 1,'213213fdas21'
Union All
select 1,'213213afdsa21'
Union All
select 1,'213213dfa21'
Union All
select 1,'21gdaf321321'
Union All
select 2,'2g13213a21'
Union All
select 2,'21321ad321'
Union All
select 2,'2fdsa21321'
Union All
select 2,'2121321'
Union All
select 2,'8881321'
Union All
select 2,'285675'
Union All
select 2,'2321'

select *
From
(
Select SalesPersonID, OrderID, Row_NUMBER() Over (Partition By SalesPersonID Order By OrderID) as OrderRank
from tbl1
) a
where a.OrderRank <= 5[/code]

mjda
Starting Member

6 Posts

Posted - 2007-04-20 : 05:04:26

Thank you!
Always learning!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources