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]

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

[Paul]

"billyana" <bill...@hotmail.com> wrote:

>This is not perfect, ecpecially if you have the same names having equal
>salary

But, they can't. emp_name is a Primary Key, so cannot be duplicated.

> and that happens to be the max salary in the group,

You should of course NEVER EVER EVER call a field or a table anything
that even resembles a reserved word, such as group, field &c.

It adds nothing but hassle (esp for migrations!), believe me, I know!

I have made a table with the DDL given at the bottom of this post and
added the employees as per your input and run your query which works
nicely, when the salaries are different.

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

However another problem arises if, let's say, Betty and Olga in Sales
have the same salary, there is duplication. Basically in order for the
query to make any sense, there has to be an emp_group in the resut, a
la

select emp_name, emp_group, max(emp_sal)
from Jared
group by emp_name, emp_group
having max(emp_sal) in (select max(emp_sal) from Jared group by
emp_group)
ORDER BY emp_group

Just a thought.

Paul...

CREATE TABLE JARED
(
EMP_NAME VARCHAR(20) NOT NULL,
EMP_GROUP VARCHAR(20) NOT NULL,
EMP_SAL INTEGER NOT NULL,
PRIMARY KEY (EMP_NAME)
);

INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Alex',
'Dev', 1000);
INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Aron',
'Dev', 1000);
INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Bill',
'Qua', 1000);
INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Dave',
'Qua', 1000);
INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Olga',
'Sal', 1000);
INSERT INTO JARED (EMP_NAME, EMP_GROUP, EMP_SAL) VALUES ('Beth',
'Sal', 1000);

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

if Oracle group then
db := Oracle 9.2.0.1.0;
else
db := Interbase 6.0.2.0;
endif