Re: Get ID of ROW when using aggregate functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At 03:00 09/04/2009, you wrote:
Message-ID: <C8.A5.28416.3666CD94@xxxxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Reply-To: "Ondrej Kulaty" <kopyto911@xxxxxxxxx>
From: "Ondrej Kulaty" <kopyto911@xxxxxxxxx>
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




--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux