I want to get employee name who is drawing the highest sal in each dept

Question

Post reply

I want to get employee name who is drawing the highest sal in each dept

Nagaram

SSCrazy

Points: 2110
More actions
September 7, 2010 at 6:14 am

#229072

empno ename sal deptid
1001 raju 20000.0010
1002 rajesh2000.0020
1003 suresh3000.0030
1004 sravan5000.0020
1005 rakesh25000.0010
1006 anil 45000.0020
1007 babu 2600.0030
1008 scott 6000.0020
1009 prashant8900.0010
1010 chandu1000.0020
1011 kiran 40000.0030
1015 ranjith25000.0010
1012 sat 25000.0020

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 1

Homework? Please show what you've tried and is not working.

Key words you might want to check out are GROUP BY and MAX.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Nagaram SSCrazy Points: 2110 More actions · Answer 2

I tried with the following query...But

select ename,sal AS maxsal,deptid

from employees

where sal in(select max(sala)

from employees

group by deptid)

order by deptid

But I am getting following wrong out put

rakesh25000.0010

ranjith25000.0010

sat 25000.0020

anil 45000.0020

kiran 40000.0030

here i dont want to get a wrong record like (sat 25000.0020)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

You will get much quicker answers if you post table creation / data insertion scripts as follows:

CREATE TABLE #tblImage (

ImageID int IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[Image] image NOT NULL,

[FileName] varchar(100) NOT NULL,

FileExtension varchar(3) NOT NULL)

INSERT INTO #tblImage ([Image],[FileName],FileExtension)

VALUES ('0x307837383738373','image01','jpg')

INSERT INTO #tblImage ([Image],[FileName],FileExtension)

VALUES ('0x307837383738373','image02','jpg')

INSERT INTO #tblImage ([Image],[FileName],FileExtension)

VALUES ('0x3078333037383337333833373338333733','image03','jpg')

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 4

You can also make use of row_number() function as described here

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

sandmathan SSC Journeyman Points: 78 More actions · Answer 5

Check it out!!

select ename,Ra.sal,Ra.deptid

from salary_raj as Ra

Inner Join (select max(sal) as S,deptid

from salary_raj

group by deptid) as P on P.S=Ra.sal and Ra.deptid=P.deptid

order by Ra.deptid

Hope this helps.

Cheers

Grant Fritchey SSC Guru Points: 398707 More actions · Answer 6

ranjith.Ileni (9/7/2010)
I tried with the following query...But
select ename,sal AS maxsal,deptid
from employees
where sal in(select max(sala)
from employees
group by deptid)
order by deptid
But I am getting following wrong out put
rakesh25000.0010
ranjith25000.0010
sat 25000.0020
anil 45000.0020
kiran 40000.0030
here i dont want to get a wrong record like (sat 25000.0020)

Yeah, the problem is you're basically joining on the salary. That's not what you ought to be joining on. Actually, you can do this query without a subselect or a join if you just look at it the right way.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Cath Trimble SSCarpal Tunnel Points: 4201 More actions · Answer 7

Check out the syntax for RANK in BOL - it'll do the trick for you here.

george sibbald SSC Guru Points: 104210 More actions · Answer 8

select ename, sal from employees where job_desc = 'dba'

works for me 🙂

---------------------------------------------------------------------

WayneS SSC Guru Points: 95409 More actions · Answer 9

Madhivanan-208264 (9/7/2010)
You can also make use of row_number() function as described here
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Will this work correctly if you have two employees in the same department with the same highest salary?

rakesh 25000.00 10
ranjith 25000.00 10

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Derrick Smith SSChampion Points: 10833 More actions · Answer 10

george sibbald (9/7/2010)
select ename, sal from employees where job_desc = 'dba'
works for me 🙂

Don't forget to enable filestream for the job_responsibilities field if we're including DBAs.

Nagaram SSCrazy Points: 2110 More actions · Answer 11

Nagaram

SSCrazy

Points: 2110

September 7, 2010 at 10:18 pm

#1217705

Thanks now it isworking

luckysql.kinda SSCertifiable Points: 7816 More actions · Answer 12

Ur solution:

SELECT emp.empno,emp.empname,tmp.Dept ,emp.SAL

FROM empdtls emp

INNER JOIN

(SELECT Max(Sal) Salary, Dept

FROM Empdtls

Group by Dept)tmp

ON tmp.dept = emp.dept

WHERE Salary = emp.Sal

order by tmp.Dept

Create table and insert data using following query:

CREATE TABLE EmpDtls(empno INT, EMPNAME VARCHAR(20),SAL INT, DEPT INT)

INSERT INTO EmpDtls VALUES(1001,'raju',20000,10)

INSERT INTO EmpDtls VALUES(1002,'rajesh',2000,20)

INSERT INTO EmpDtls VALUES(1003,'suresh',3000,30)

INSERT INTO EmpDtls VALUES(1004,'sravan',5000,20)

INSERT INTO EmpDtls VALUES(1005,'rakesh',25000,10)

INSERT INTO EmpDtls VALUES(1006,'anil',45000,20)

INSERT INTO EmpDtls VALUES(1007,'babu',2600,30)

INSERT INTO EmpDtls VALUES(1008,'scott',6000,20)

INSERT INTO EmpDtls VALUES(1009,'prashant',8900,10)

INSERT INTO EmpDtls VALUES(1010,'chandu',1000,20)

INSERT INTO EmpDtls VALUES(1011,'kiran',40000,30)

INSERT INTO EmpDtls VALUES(1015,'ranjith',25000,10)

INSERT INTO EmpDtls VALUES(1012,'sat',25000,20)

pdeokar SSC Journeyman Points: 76 More actions · Answer 13

HI FREINDS,

USING FOLLOWING QRY AS REFERENCE FOR YOUR QRY

SELECT max(item_rate),Grp_ID FROM item_mast

WHERE Grp_ID IN ('001') AND item_rate NOT IN (SELECT max(IM.item_rate) FROM item_mast IM

WHERE Grp_Id=im.Grp_ID GROUP BY Grp_ID) AND ItemCode<>''

GROUP BY Grp_ID ORDER BY Grp_ID

PLEASE CHECK AND REVERT BACK TO ME...

Luzi62 Ten Centuries Points: 1218 More actions · Answer 14

Derrick Smith (9/7/2010)
george sibbald (9/7/2010)
select ename, sal from employees where job_desc = 'dba'
works for me 🙂
Don't forget to enable filestream for the job_responsibilities field if we're including DBAs.

lol and change the salary to decimal(18,2) if you also be payed for the work you have to do for others eg. "my program is slow it must be the Db" crap *lol*