Query question

  • Thread starter Thread starter Ac
  • Start date Start date
A

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!
 
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;
 
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!



KARL DEWEY said:
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


Ac said:
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!
Click to expand...
Click to expand...
 
Hi Brain,

I did not get this part of method 1.
Criteria: In (Select Top 3 [UnitsInStock] From Products Where _
Click to expand...
Click to expand...
[CategoryID]=[Categories].[CategoryID] Order By [UnitsInStock] Desc)

If I only have one table, how can I modify this Criteria? Thanks!
 
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
'====================================================
 
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.


John Spencer said:
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
'====================================================


KARL said:
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;
Click to expand...
Click to expand...
 
Back
Top