Get ID of ROW when using aggregate functions

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

Re: Get ID of ROW when using aggregate functions

--0016e642d7fea11ca8046708eb71
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

> 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

--0016e642d7fea11ca8046708eb71--

Re: Get ID of ROW when using aggregate functions

--000e0cd1555233cbab04670900ba
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 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
>
>

--000e0cd1555233cbab04670900ba--

Re: Get ID of ROW when using aggregate functions

At 03:00 09/04/2009, you wrote:
>Message-ID:
>To: php-db@lists.php.net
>Reply-To: "Ondrej Kulaty"
>From: "Ondrej Kulaty"
>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