Get ID of ROW when using aggregate functions
| From: | Ondrej Kulaty | Date: | Wed, 08 Apr 2009 08:54:55 +0000 |
| Subject: | Get ID of ROW when using aggregate functions | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-46100@lists.php.net to get a copy of this message | ||
Hi,
I have following table:
id int(11)
name varchar(255)
company varchar(255)
sallary int(11)
CREATE TABLE
test (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
company varchar(255) NOT NULL,
sallary int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 AUTO_INCREMENT=1 ;
With rows:
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?
Thanks for any help.