employees and their department who is top salary

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

employees and their department who is top salary

Author

Topic

amalshah71
Starting Member

9 Posts

Posted - 2006-12-04 : 05:45:12

i have 2 tables emp and dept

emp has columns:
empid(pk),empname,deptid(fk),salary

dept has columns:
deptid(pk),deptname

now my aim is:
List of the employees and their department who is top salary earner of the department.

wht i can think of is:

select distinct empname,deptname,max(salary) as 'max salary'
from emp e,dept d
where e.deptid=d.deptid
group by empname,deptname

but it gives unexpected result...

help appreciated

cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 06:03:15

[code]declare @dept table
(
deptno int,
dept varchar(100)
)

declare @emp table
(
eno int,
ename varchar(100),
salary numeric(9,2),
deptno int
)

insert @dept select 1, 'sales' union all select 2, 'IT' union all select 3, 'Production'

insert @emp
select 1, 'aa', 17790, 1 union all
select 2, 'bb', 7575, 2 union all
select 3, 'cc', 5290, 3 union all
select 4, 'dd', 8760, 1 union all
select 5, 'ee', 6340, 2 union all
select 6, 'ff', 10000, 3 union all
select 7, 'gg', 8672, 1 union all
select 8, 'hh', 1563, 2 union all
select 9, 'jj', 5854, 3

select d.dept, e.ename, e.salary
from @emp e join @dept d
on e.deptno = d.deptno
where salary = (select max(salary) from @emp e1 where e1.deptno = d.deptno)
[/code]

OR alternatively,

[code]select d.dept, e.ename, e.salary
from @emp e
join
(
select d.deptno, d.dept, max(salary) salary
from @emp e join @dept d on e.deptno = d.deptno
group by d.deptno, d.dept
) d
on e.deptno = d.deptno
and e.salary = d.salary[/code]

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:05:50

select e.empid, e.empname, d.deptid, d.deptname, e.salary,
case when e.salary = q.msa then 'Yes' ELSE 'No' END [Is Top Salary Earner]
from emp e inner join dept d on d.deptid = e.deptid
inner join (select deptid, max(salary) msa from emp group by empid) q on q.deptid = d.deptid

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 06:10:04

~~group by empid~~ should be group by deptid !!!

Peter Larsson
Helsingborg, Sweden

amalshah71
Starting Member

9 Posts

Posted - 2006-12-05 : 04:00:10

this is how i manage to do it....different from the above ones.

select empname,deptname,max(salary) as 'max salary'
from emp e,dept d
where e.deptid=d.deptid
group by empname,deptname,d.deptid,salary
having max(salary)= any(select max(salary) from emp e,dept d where e.deptid=d.deptid group by e.deptid)

nyways thnks to those who helped...

cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 04:07:55

Your query has many things which are redundant for e.g. Group By clause in the inner query. Also, outer Group By clause is not needed. Check my first solution.

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 04:12:06

What is to expect when one does not want to learn?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 04:17:30

True!!

I don't know why people often go for complicated things when simple solutions exists.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 06:13:03

quote:
Originally posted by Peso

What is to expect when one does not want to learn?

Peter Larsson
Helsingborg, Sweden

He may be proud of writing the code that was not suggested

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 06:41:52

Yes, of course!
But the code will not work efficiently. And (in my opinion) bloated.
I don't think he knows what he have written. Maybe he got the query from someone else.
Or used some kind of guide in access to write the query.

GROUPing by Salary and also picking MAX(Salary)? He could very easy skip the MAX part, and the full GROUP BY part.
Also substituting "HAVING MAX ... = " with simple "Salary = " only.
He is also using non-ansi SQL syntax. And the correlated subquery does not need join at all.

But after all this, the query is as Harsh wrote.

He wanted help, and we did help him.
But he did not take in the suggestions not learned from them.

I was once in the same situation (a newbie) but I did learn from those smarter than me because I took my time to study the query to see what was going on. And I still do here at SQLTeam, because here are many smart people.
So I can understand that he is proud about his own creation but he has learned nothing.

And OP first code for query is almost there. It only needs a correlated subquery to work (without distinct)!
In my opinion he took a step back with second query.

Peter Larsson
Helsingborg, Sweden

amalshah71
Starting Member

9 Posts

Posted - 2006-12-05 : 08:09:30

NOTE : i am 4 days old with respect to my SQL learning and knowledge

i could not understand the queries involving the (@) symbol....

to those who say "What is to expect when one does not want to learn?"

thnk u sir for ur help....but was lookin for help at my level where am just learning things(SQL)....i just could not understand wht u wrote...may be i am not as yet exposed to the CONCEPT with which u tried to explain

to those who say "Your query has many things which are redundant "

i know at my starting days of learning things(SQL) i will be makin lot of stupid mistakes(things which are redundant) but by making these........i would learn...

to those who say "I don't know why people often go for complicated things when simple solutions exists."

my aim is to learn by self-developing things...and look at others solution and LEARN from it....not to just implement the EXISTING SIMPLE SOLUTION...if i just do tht.......i won't accomplish my aim of learning SQL.....

to those who say "He may be proud of writing the code that was not suggested"

i was happy when i reached to a solution...and thought of putting on the forum...thinking that it MIGHT help ppl who r at my LEVEL of learning SQL.......

thnks to all those who helped in their own way.....

cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 08:12:16

So which parts of the query confuses you?

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 08:18:00

My dear friend,

We are not criticizing you for you have written complicated query, we are pointing out the things which are not needed and boils down to the same solution we gave earlier.

Also, look from the performance point of view...writing query that bring expected output is not enough...You have to learn how to find multiple solutions and choosing best among them.

Next time somebody criticizes you, take it in positively, it will only help you, not us.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 09:54:53

>>NOTE : i am 4 days old with respect to my SQL learning and knowledge

http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

>>i could not understand the queries involving the (@) symbol....

The one described is table variable

>>thinking that it MIGHT help ppl who r at my LEVEL of learning SQL.......

Thats good anyway

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources