List:General Discussion« Previous MessageNext Message »
From:Victoria Reznichenko Date:March 19 2004 10:01am
Subject:Re: writing queries to get distinct results
View as plain text  
"Casey Sheridan" <casey@stripped> 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
 / /|_/ / // /\ \/ /_/ / /__   Victoria.Reznichenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
       <___/   www.mysql.com




Thread
writing queries to get distinct resultsCasey Sheridan19 Mar
  • Migrating Access databases to MySQLArjun Subramanian19 Mar
    • Re: Migrating Access databases to MySQLKaram Chand19 Mar
    • AW: Migrating Access databases to MySQLFreddie Sorensen19 Mar
    • Re: Migrating Access databases to MySQLMartijn Tonies19 Mar
  • Re: writing queries to get distinct resultsVictoria Reznichenko19 Mar
RE: Migrating Access databases to MySQLArjun Subramanian19 Mar
  • Re: Migrating Access databases to MySQLBob Ramsey19 Mar