SQL Query

[jared]

This is probably easy and I'm missing it because I'm a little
sleep-deprived this month, but:

given the following table 'test':

name group salary
----- ----------- ------
unique medium cardinality


How can I select the name(s) for each group that have the highest
salary for that group?

[billyana]

Re: SQL Query


jared wrote:

This is probably easy and I'm missing it because I'm a little
sleep-deprived this month, but:
>
given the following table 'test':
>
name group salary
----- ----------- ------
unique medium cardinality
>
>
How can I select the name(s) for each group that have the highest
salary for that group?

This is not perfect, ecpecially if you have the same names having equal
salary and that happens to be the max salary in the group, but it will
give you an idea:

Here is the data:
Name Group Salary
Alex Dev 1000
Aron Dev 500
Bill QA 3000
David QA 9000
Olga Sales 900
Betty Sales 5200

Here is the query:
select name, max(salary)
from test
group by name
having max(salary) in (select max(salary) from test group by "group")

This is the result:
Name MAX(SALARY)
Alex 1000
Betty 5200
David 9000