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
