Hi !
zhihuali wrote:
Hi netters,
Suppose I have a table X:
Name Salary
Tom 1000
Jack 3000
Mike 1000
Mike 3500
Tom 4000
I want to do the following thing:
First, order by Salary;
Second, starting from the highest salary down, group by Name. So the resulting
table would be:
Tom 4000
Tom 1000
Mike 3500
Mike 2000
Jack 3000
If I order by Salary,Name, the Name will not be grouped together. Can anyone
help me with this?
Disclaimer: I did not check this, please try yourself.
I doubt this can be done using SQL just on that table x.
It should work using an additional table and a join:
CREATE TABLE x_tmp (name .... , max_sal .... );
DELETE x_tmp ;
INSERT INTO x_tmp SELECT name, MAX(salary) FROM x GROUP BY name ;
SELECT name, salary
FROM x INNER JOIN x_tmp USING (name)
ORDER BY x_tmp.max_sal DESC ;
For the sake of this example, using "name" is ok, but in reality I fear
names will not be unique, so you might need some really identifying
value (emp-number or such).
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
