Re: Get ID of ROW when using aggregate functions

At 03:00 09/04/2009, you wrote:

Message-ID: <C8.A5.28416.3666CD94@pb1.pair.com> To: php-db@lists.php.net Reply-To: "Ondrej Kulaty" <kopyto911@gmail.com> From: "Ondrej Kulaty" <kopyto911@gmail.com> Date: Wed, 8 Apr 2009 10:54:55 +0200 Subject: Get ID of ROW when using aggregate functions INSERT INTO test (id, name, company, sallary) VALUES (1, 'Jane', 'Microsoft', 10000), (2, 'Peter', 'Novell', 12000), (3, 'Steven', 'Microsoft', 17000); I want to select person from each company with a highest sallary. I run this SQL: SELECT id,name,company,MAX(sallary) FROM test GROUP BY company; And result is:

id     name     company     MAX( sallary )
1     Jane       Microsoft   17000
2     Peter      Novell        12000

Why it returned Jane (id 1) as a person with highest sallary (17000) when obviously Jane has sallary of 10 000?

Are you expecting a person to have more than one sallary ? Your example rows don't indicate that. If the person is unique in this table, then you just need to order by salary : SELECT id, name, company, sallary FROM test ORDER BY sallary DESC LIMIT 1; If for some reason the person appears twice (perhaps you're paying them twice - I'd like their job please !) then SELECT id, name, company, MAX(sallary) AS top_salary FROM test GROUP BY sallary HAVING sallary = top_salary; You shouldn't really use LIMIT here though, because - though I didn't indicate it in the simple ORDER BY above, 2 people might have the same (top) salary of 17000 - rather than the person appearing twice, the salary appears twice (or more) and includes matching rows for the MAX() value. If you're considering microsoft and novell, probably those values need at least another zero on the end, including stock options ;-) It seems to me, if you intend the person to appear once, make the name column use a UNIQUE KEY. Since people will (eventually) have the same name, e.g 2x John Smiths or 2x Peters working at Novell, use some unique proxy for person, such as social security number or employee number + company, or similar. HTH Cheers - Neil