Thursday, August 6, 2009

SQL Query: Find second highest salaried employee ?

Here's the SQL code:-

--Returns name and salary of employee assuming EMPLOYEE and SALARY are different tables with
--column ID connecting both
SELECT NAME,SALARY FROM EMPLOYEE INNER JOIN SALARY ON EMPLOYEE.ID=SALARY.ID WHERE SALARY IN(
  --following query returns required salary (Second largest in example)
   SELECT MAX(SALARY) FROM SALARY WHERE SALARY NOT IN
   (
     --follwing query returns top x(1 in example) salaries
     --replace x to 0,1,... to get highest, second highest,...
     SELECT TOP 1 SALARY FROM SALARY ORDER BY salary DESC
   )
)




SQL Query to find THIRD highest salaried employee:-

SELECT name,

       salary

FROM   employee

       INNER JOIN salary

         ON employee.id = salary.id

WHERE  salary IN (SELECT Max(salary)

                  FROM   salary

                  WHERE  salary NOT IN (SELECT   TOP 2 salary

                                        FROM     salary

                                        ORDER BY salary DESC))

2 comments:

  1. I know this if off topic but I'm looking into starting my own blog and was wondering what all is needed to get
    set up? I'm assuming having a blog like yours would cost a pretty penny?
    I'm not very internet smart so I'm not 100% sure.
    Any tips or advice would be greatly appreciated.
    Thank you

    my weblog: download facebook Hacker

    ReplyDelete
  2. I'm really enjoying the design and layout of your site.

    It's a very easy on the eyes which makes it
    much more pleasant for me to come here and visit more often.
    Did you hire out a designer to create your theme?
    Superb work!

    Here is my web page :: homepage

    ReplyDelete

Was the information useful?

Followers