Query question

[Ac]

Hi,

I have a table collecting the data for division, people and cost. How can I
write a query to find out who are the top 5 people spent most of the money in
each division of the company? Thanks!

 

[Brian]

Here is a MS link that explains how:

http://support.microsoft.com/kb/210039

 

[KARL DEWEY]

Try this --
SELECT Q.[division], Q.[people], Q.[cost], (SELECT COUNT(*) FROM [YourTable]
Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1)<=5))
ORDER BY Q.[division], Q.[cost] DESC;

 

[Ac]

Hi Karl,

Thank you for your reply.

I have tried your code, it selected all 5 people from each division, but
there are the bottom 5 people (least cost), I would like to find out the top
5 people had most cost in each department. Could you help me again! Thanks!

Try this --
SELECT Q.[division], Q.[people], Q.[cost], (SELECT COUNT(*) FROM [YourTable]
Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1)<=5))
ORDER BY Q.[division], Q.[cost] DESC;

--
KARL DEWEY
Build a little - Test a little

Hi,

I have a table collecting the data for division, people and cost. How can I
write a query to find out who are the top 5 people spent most of the money in
each division of the company? Thanks!

 

[Ac]

Hi Brain,

I did not get this part of method 1.

Criteria: In (Select Top 3 [UnitsInStock] From Products Where _

[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

If I only have one table, how can I modify this Criteria? Thanks!

 

[John Spencer]

Try reversing the rank comparison

SELECT Q.[division], Q.[people], Q.[cost]
, (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] > Q.[cost])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] > Q.[cost])+1)<=5))
ORDER BY Q.[division], Q.[cost] DESC;

An alternative which may be faster would be to use something like the
following.

SELECT Q.[division]
, Q.[people]
, Q.[cost]
, Count(Q1.Division)+1 AS Rank
FROM YourTable AS Q LEFT JOIN YourTable as Q1
ON Q.Division = Q1.Division
AND Q.Cost > Q1.Cost
GROUP BY Q.[division], Q.[people], Q.[cost]
HAVING Count(Q1.Division) +1 <=5

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ac]

Hi John,

It works, Thanks! The only place I modified was added ORDER BY Q.[division],
Q.[cost] DESC and reverse the sign '>' in AND Q.Cost < Q1.Cost in the second
version.

Try reversing the rank comparison

SELECT Q.[division], Q.[people], Q.[cost]
, (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] > Q.[cost])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] > Q.[cost])+1)<=5))
ORDER BY Q.[division], Q.[cost] DESC;

An alternative which may be faster would be to use something like the
following.

SELECT Q.[division]
, Q.[people]
, Q.[cost]
, Count(Q1.Division)+1 AS Rank
FROM YourTable AS Q LEFT JOIN YourTable as Q1
ON Q.Division = Q1.Division
AND Q.Cost > Q1.Cost
GROUP BY Q.[division], Q.[people], Q.[cost]
HAVING Count(Q1.Division) +1 <=5

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Try this --
SELECT Q.[division], Q.[people], Q.[cost], (SELECT COUNT(*) FROM [YourTable]
Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[division] = Q.[division]
AND Q1.[cost] < Q.[cost])+1)<=5))
ORDER BY Q.[division], Q.[cost] DESC;