Re: [PHP-DB] 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 <[email protected]> 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
>
>