"Casey Sheridan" <[EMAIL PROTECTED]> wrote:
> I have a table that has employee names, pay rates, and unique IDs. I want
> to select all of the distinct employee names, and if there are two employees
> with the same name, I want to be able to choose only one; the one with the
> highest pay rate. If there are two identical employee names with the same
> pay rate, I want to select the one that has the lowest unique ID number.
> Can anyone help me on how to write this query? Thanks!
If your version of MySQL supports subqueries (>=4.1.0):
SELECT name, pay_rate, MIN(id)
FROM table
WHERE (name, pay_rate) IN
(SELECT name, MAX(pay_rate) FROM table GROUP BY name)
GROUP BY name, pay_rate;
Otherwise you can't do it with one query. You can rewrite the above query using
temporary table and JOIN.
CREATE TEMPORARY TABLE tmp
SELECT name, MAX(pay_rate) pay_rate FROM table GROUP BY name;
SELECT table.name, table.pay_rate, MIN(table.id)
FROM table, tmp
WHERE table.name=tmp.name AND
table.pay_rate=tmp.pay_rate
GROUP BY table.name, table.pay_rate;
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
