Re: SQL Query

"billyana" <billyana_at_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,

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 Received on Thu Mar 17 2005 - 02:20:59 CET