Query question ... group by

[aopen]

Table has the following fields:
1. office
2. person
3. amount

I need to get list for every office, person with highest amount. In the format below:

Office, Person name, sum(amount)

Office #1 - John Smith - $1000

In the query I group by 'office', 'person'. It give me total amount but not for the correct person.

Can this be done in one query?

[NogDog]

I'm not 100% sure about this, but I believe you can do:

SELECT office, person, max(amount) FROM table_name GROUP BY office

[aopen]

NogDog wrote:

I'm not 100% sure about this, but I believe you can do:

SELECT office, person, max(amount) FROM table_name GROUP BY office

It will return max(amount) per office only it doesn't belong to right person...

[NogDog]

That's the main thing I wasn't 100% sure about. 🙁

You might want to take a look at this article for some other options.