Finding The top Expense of an employee in a month

[callnish]

Hi,
I have a table that lists the expenses of all employees in the company in a month. So the table has three columns- number, employee ID, Expense

I want to make a query that could list me the maximum expense made by all employees. In other words, my table has multiple expenses for each employee, I just want to know the ID of that employee and the maximum expense made by him/her.

Thanks in advance!
Sean

 

[PHV]

Something like this ?
SELECT employeeID, MAX(Expense) maxExpense
FROM yourTable
GROUP BY employeeID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[callnish]

Thanks a lot for your reply!

I have just one more thing that I forgot to mention. I want to get the expenses made by employees in Descending order. I tried adding a clause like "order by Expense Desc" but it asked me to put the Expense field also in the Group by Clause. When I did that, then instead of getting 25 records I am getting 59 records. I should get 25 records because there are only 25 Unique Employee IDs in the table.

Thanks!

 

[PHV]

SELECT employeeID, MAX(Expense) maxExpense
FROM yourTable
GROUP BY employeeID
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[callnish]

That did it! Thanks a lot, I really appreciate your correct and quick replies.