CODEWITHSUNDEEP
sql-server-2008
Arbaaz
Arbaaz

Reputation: 321

SQL query to find out third highest salary involving two tables

I have two tables:

Persons:

empid(primary key)
firstname
lastname
email

Details:

Did(primary key)
salary
designation
empid

Now I want to select firstname, lastname, salary of the employee with the 3rd highest salary.

KEEP IT SIMPLE SINCE I AM A BEGINNER.

Also it would be great if you can suggest me a site where I can find SQL query exercises with solutions (I do not need an online interpreter, I have SQL Server 2008 to practice on ), I have completed w3schools (which was awesome) but I need exercises now, I tried sql.ex.ru but it was not that helpful.

Upvotes: 0

Views: 4700

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Here is a little trick. What it does is add a sequence number or row number onto each row, giving the ordering by salary. It then just chooses the third one:

select FirstName, LastName, Salary
from (select p.FirstName, p.LastName, d.salary,
             row_number() over (order by salary desc) as seqnum
      from persons p join
           details d
           on p.empid = d.empid
     ) t
where seqnum = 3

The row_number() function does this calculation. It is a very useful function, that you can add to your SQL knowledge.

Upvotes: 1

Arbaaz
Arbaaz

Reputation: 321

SELECT TOP 1 persons.FirstName, persons.LastName, Salary 
FROM persons inner join Details
ON persons.empid=Details.empid 
where Salary not in(SELECT TOP 2 Salary from Details ORDER BY Salary DESC)
order by Salary desc

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24086

try this:

SELECT   top 1 P.*,a.salary 
FROM     Persons p
JOIN
         (select top 3 empid,salary from Details order by salary desc)a
ON       p.empid=a.empid
ORDER BY p.salary

or

;WITH CTE AS
 (SELECT *,
         ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_NUM
 FROM DETAILS)
 SELECT * 
 FROM  PERSONS P
 JOIN  CTE C
 ON    P.EMPID=C.EMPID
 where c.ROW_NUM=3

Upvotes: 1

Related Questions