Top 10 within each 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 2000 Forums

Transact-SQL (2000)

Top 10 within each group by

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-24 : 08:21:21

Gerry writes "Hi

I Use SQL 2k and here is my problme

Suppose many customers within each country of Europe each with a sales value. I need to show for each country the top 10 customers by sales value descending.

e.g.
England CustA 5000
England CustC 4000
England CustZ 3000
Ireland CustID 300
Ireland CustIE 250.......

top 10 will give me the top 10 in the total record set but how do I get the above please ?

Thanks for your consideration

Gerry "

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-24 : 09:31:27

Read Me.

Jay White
{0}

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-24 : 09:31:33

[code]select country,cust,sales
from custsales cs1
where cust in
(
select top 10 cust
from custsales cs2
where cs2.country = cs1.country
order by sales desc
)
[/code]

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-24 : 09:33:00

More study I see. Thanks again Jay.

Gerry.Perry@Avnet.com
Starting Member

6 Posts

Posted - 2004-02-24 : 12:22:35

Absolutely right

To be honest I'm not TOO sure why it works.... but it does.

Thanks

Gerry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources