Re: Get ID of ROW when using aggregate functions

there is probably a better way to achieve this, but, this is a quick solution mysql> SELECT * FROM test where sallary IN (SELECT MAX(SALLARY) FROM test GROUP BY company); +----+--------+-----------+---------+ | id | name | company | sallary | +----+--------+-----------+---------+ | 2 | Peter | Novell | 12000 | | 3 | Steven | Microsoft | 17000 | +----+--------+-----------+---------+ 2 rows in set (0.00 sec) the answer to your question might lie by looking at this: mysql> SELECT * FROM test GROUP BY company; +----+-------+-----------+---------+ | id | name | company | sallary | +----+-------+-----------+---------+ | 1 | Jane | Microsoft | 10000 | | 2 | Peter | Novell | 12000 | +----+-------+-----------+---------+ 2 rows in set (0.00 sec) SQL experts here please enlighten us.. On Wed, Apr 8, 2009 at 6:54 PM, Ondrej Kulaty <kopyto911@gmail.com> wrote: > 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. > > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >