Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.

TSQL Beginners Challenge 1 - Find the second highest salary for each department

Simple solution with 2 inner join (not very elegant)

May 2 2012 12:00AM by cverde   

Simple solution with 2 inner join, not very elegant but works at least on SQL 2008.

SELECT  e.*
FROM    @Employees e
        INNER JOIN ( SELECT MAX(sec.salary) AS salary_sec ,
                            sec.Department
                     FROM   @Employees sec
                            INNER JOIN ( SELECT MAX(Salary) AS Salary_MAX ,
                                                Department
                                         FROM   @Employees
                                         GROUP BY Department
                                       ) m ON m.Department = sec.Department
                     WHERE  Salary != Salary_MAX
                     GROUP BY sec.Department
                   ) sec ON sec.Department = e.Department
WHERE   Salary = Salary_sec

Tags:


cverde
1380 · 0% · 16
2 Readers Liked this
Jacob Sebastian Liked this on 5/3/2012 8:30:00 AM
Profile · Blog · Facebook · Twitter
Guru Samy Liked this on 5/10/2012 1:09:00 AM
Profile · Blog
2
Liked



Submit

8  Comments  

  • Why wouldn't you just use the RANK or ROW_NUMBER functions within in CTE?

    commented on May 2 2012 9:56AM
    Marc Jellinek
    95 · 2% · 586
  • I think it'll be more easier if the ROW_NUMBER() is used.

    commented on May 2 2012 8:34PM
    lyqandgdp
    338 · 0% · 124
  • If I used row_number(), i can only exclude the first line based on this, but what if two employees have the same top salary ?

    With salary in the range of 15000, 15000, 10000, 5000 : i will return 15000 although the second is 10000, right ?

    commented on May 3 2012 3:26AM
    cverde
    1380 · 0% · 16
  • I think you can get the Depart and Salary in the CTE, then you join the CTE with CTE itself by Department and Salary.

    Here is my example, I think it works. ;WITH T AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Ord FROM @Employees ) SELECT * FROM T t1 WHERE EXISTS ( SELECT * FROM T t2 WHERE t1.Department = t2.Department AND t2.Ord = 2 AND t1.Salary=t2.Salary

    )

    commented on May 3 2012 4:42AM
    lyqandgdp
    338 · 0% · 124
  • @cverde: you are right. Using ROW_NUMBER() will not account for ties. RANK() will.

    commented on May 3 2012 5:46AM
    Marc Jellinek
    95 · 2% · 586
  • It's get a lot trickier when considering tie ! =D But already pretty instructive for a beginner puzzle.

    @lyqandgdp : Thanks for your solution, i will try to use more CTE enven if i'm not so comfortable with them.

    @Marc Jellinek : Indeed Rank() is worthly but I'm still struggling because when there is a tie, it numbers them : '1,1,3' and then '1,1,1,4', ... so no number 2 to look for. But I thinks it's the best way. Thanks for your help.

    commented on May 3 2012 7:49AM
    cverde
    1380 · 0% · 16
  • Finally found the solution, we have to use DENSE_RANK() to avoid the gap in rank.

    I will post another answer with it.

    commented on May 3 2012 11:56AM
    cverde
    1380 · 0% · 16
  • you would be in difficulty if user change requirement to ask 10th highest salary for department....

    commented on Nov 6 2012 8:06AM
    eshant.garg
    1999 · 0% · 9

Your Comment


Sign Up or Login to post a comment.

"Simple solution with 2 inner join (not very elegant)" rated 5 out of 5 by 2 readers
Simple solution with 2 inner join (not very elegant) , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]