TOP N BY GROUP

JElster
JElster used Ask the Experts™
on
Hi.. I have table of sales reps and products and sales..  I need the top 10 sales persons by product


name             product              sales
john                tv                          100
mary              phone                    50
john               phone                    10
bob                radio                        5
mary             clock                        59




using sql 2005 !
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Database and Application Developer
Commented:
;with cte
As
(
Select name, product, sales ,
dense_rank() Over (partition by product Order By sales Desc) As rk
From SalesRepTable Order By name;
)
Select name, product, sales  From cte
Where rk <= 10;

Modified...

Author

Commented:
I think the server is 2000

'dense_rank' is not a recognized function name.


Any way to do in SQL2000?
Mike EghtebasDatabase and Application Developer

Commented:
try:

Select Top 10 name, product, SumSale 
From 
( 
Select name, product, Sum(sales) As SumSale
From MyTable
Group By product, name
) As D ( name, product, SumSale)
Order By product, SumSale Desc, name 

Open in new window


modified...

Author

Commented:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions

Any ideas?
Mike EghtebasDatabase and Application Developer

Commented:
The ORDER BY clause is invalid in views, inline functions, derived tables, and common table expressions unless top(x) and offset fetch is utilized.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial