June 24, 2011 at 10:40 am
Hi All,
I have a table called EMPLOYEE in which i have following fields & records
Emp_Name Salary Dept
aaa 10000 a
bbb 16662 a
rrr 73637 a
jhfdj 87683 b
jhk 7382 b
ewkjhk 98798 b
khkds 79898 a
jhjhj 79879 b
I am trying to write a query to find highest paying two employees for each department.
But unable to get results..
Thanks in Advance
June 24, 2011 at 11:04 am
Search BOL for the "OVER" clause and "ROW_NUMBER"
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJune 28, 2011 at 2:36 pm
Here is a start for you this will give you the highest paid per department
select Emp_Name max(Salary)
group by emp_name, Dept
***The first step is always the hardest *******
June 28, 2011 at 2:46 pm
glen.wass (6/28/2011)
Here is a start for you this will give you the highest paid per department
select Emp_Name max(Salary)
group by emp_name, Dept
That's great for the highest, it's much harder to extend to the highest 2 than the row_number option that Jason alluded to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2011 at 1:45 am
Please check the below.
CREATE TABLE tblGetEmp (EmpName CHAR(8),Salary INT,Dept CHAR(1))
INSERT INTO tblGetEmp
SELECT 'aaa',10000,'a' UNION ALL
SELECT 'bbb',16662,'a' UNION ALL
SELECT 'rrr',73637,'a' UNION ALL
SELECT 'jhfdj',87683,'b' UNION ALL
SELECT 'jhk',7382,'b' UNION ALL
SELECT 'ewkjhk',98798,'b' UNION ALL
SELECT 'khkds',79898,'a' UNION ALL
SELECT 'jhjhj',79879,'b'
SELECT
* FROM(
SELECT
ROW_NUMBER() OVER(PARTITION BY Dept ORDER BY Salary) AS Row,
EmpName,Salary,Dept
FROM tblGetEmp) AS T
WHERE Row > 2
June 30, 2011 at 7:32 am
sqlusers (6/30/2011)
Please check the below.CREATE TABLE tblGetEmp (EmpName CHAR(8),Salary INT,Dept CHAR(1))
INSERT INTO tblGetEmp
SELECT 'aaa',10000,'a' UNION ALL
SELECT 'bbb',16662,'a' UNION ALL
SELECT 'rrr',73637,'a' UNION ALL
SELECT 'jhfdj',87683,'b' UNION ALL
SELECT 'jhk',7382,'b' UNION ALL
SELECT 'ewkjhk',98798,'b' UNION ALL
SELECT 'khkds',79898,'a' UNION ALL
SELECT 'jhjhj',79879,'b'
SELECT
* FROM(
SELECT
ROW_NUMBER() OVER(PARTITION BY Dept ORDER BY Salary) AS Row,
EmpName,Salary,Dept
FROM tblGetEmp) AS T
WHERE Row > 2
This doesn't give the results requested. Your limited data sample is skewing your results to make it APPEAR to work, but it only gives the correct results when EACH DEPARTMENT HAS EXACTLY FOUR EMPLOYEES. Try testing it with departments with 1, 2, 3, or 5 employees.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 30, 2011 at 9:50 am
Indeed. To fix, flip the sort order around and flip the comparison.
SELECT EmpName, Salary, Dept FROM (
SELECT EmpName,Salary,Dept, ROW_NUMBER() OVER(PARTITION BY Dept ORDER BY Salary DESC) AS Row,
FROM tblGetEmp) Sub
WHERE Row <=2
Of course, that doesn't handle the case where there are multiple employees in a Dept that have the same salary where that salary is the highest. Don't know what the OP wants in that case.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2011 at 9:52 am
1. Reverse the ORDER BY in the ROW_NUMBER()
2. Reverse the WHERE to be < 3
3. As a general rule, use table variables or temp tables in your questions/answers. Nobody wants to have a bunch of tables being created during testing. 🙂
CREATE TABLE #tblGetEmp
(EmpName CHAR(8),Salary INT,Dept CHAR(1));
INSERT INTO #tblGetEmp
SELECT 'aaa',10000,'a' UNION ALL
SELECT 'bbb',16662,'a' UNION ALL
SELECT 'rrr',73637,'a' UNION ALL
SELECT 'rrr',85000,'a' UNION ALL
SELECT 'jhfdj',90000,'b' UNION ALL
SELECT 'jhfdj',87683,'b' UNION ALL
SELECT 'jhk',7382,'b' UNION ALL
SELECT 'ewkjhk',98798,'b' UNION ALL
SELECT 'rrr',73637,'x' UNION ALL
SELECT 'khkds',79898,'a' UNION ALL
SELECT 'jhjhj',79879,'b'
SELECT
*
FROM (SELECT
ROW_NUMBER() OVER(PARTITION BY Dept ORDER BY Salary DESC) AS Row,
EmpName,Salary,Dept
FROM #tblGetEmp) AS T
WHERE
Row < 3
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply