Re: Get ID of ROW when using aggregate functions

the answer is exactly what you asked for. It gave you the max salary per company and made a "lucky guess" (not really, alfabetically first) as to which name you wanted since you never specified this of the top of my head, try this. SELECT a.id,a.name,a.company,a.sallary FROM test a ,(select company,max(sallary) sallary from test group by company) b where a.company=b,company and a.sallary=b.sallary 2009/4/8 Ondrej Kulaty <kopyto911@gmail.com> > 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 > > -- Jack van Zanen ------------------------- This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation