Re: Get ID of ROW when using aggregate functions

At 03:00 09/04/2009, you wrote:
>Message-ID: <[email protected]>
>To: [email protected]
>Reply-To: "Ondrej Kulaty" <[email protected]>
>From: "Ondrej Kulaty" <[email protected]>
>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