Select top 3 per site 1

[flaviooooo]

Hey guys,

I have a table which consists of the sales figures, per person per site.

Site
A Chris 5000
A Frank 2000
A Holly 3500
A Mark 6000
A Tony 1000
A Isabelle 2500
B Harry 3000
B Peter 9000
B Willy 1050
B Jack 2000
B Ann 6500

Now I would like to have some kind of top 3 people per site
So in the example above:
A Mark 6000
A Chris 5000
A Holly 3500
B Peter 9000
B Ann 6500
B Harry 3000

Can this be done in a query?

 

[Remou]

There is a FAQ:

http://www.tek-tips.com/faqs.cfm?fid=4058

 

[flaviooooo]

Hmm... Still can't get it to work.

Here's my code:

select Site,
(SELECT top 5 StaffID
FROM Staff
where Site = S.Site
ORDER BY Sales DESC) as Person
from Staff S

It gives me the error:

At most one record can be returned by this subquery.

 

[Remou]

Try:

[tt]SELECT s.Site, s.StaffID, s.Sales
FROM Sales s
WHERE s.StaffID
IN (SELECT Top 3 t.StaffID
FROM Sales t
WHERE s.Site=t.Site
ORDER BY Sales Desc)
ORDER BY s.Site, S.Sales DESC[/tt]

 

[flaviooooo]

Looking good Remou, thanks!