CODEWITHSUNDEEP
sql-server
Arbaaz
Arbaaz

Reputation: 321

SQL query to find out third highest salary involving multiple 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.

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: 1

Views: 7035

Answers (4)

Rakesh Anand
Rakesh Anand

Reputation: 423

Suppose if you have tables with the following field values

Persons

enter image description here

Details

enter image description here

Now if you want to find the Third highest salary.Then, the query below will give you the following result set.

enter image description here

Output:

enter image description here

Upvotes: 1

podiluska
podiluska

Reputation: 51504

 select firstname, lastname, salary 
 from 
 (
 select 
    employee.*, details.salary,
    row_number() over (order by salary desc) salaryrank
 from 
      employee
 inner join 
      details
           on employee.empid = details.empid
 ) v
 where salaryrank=3

As for SQL problems with solutions, why not look on stackoverflow itself? Find a question, and see how you can get an answer. Then see what the best rated answer is

Upvotes: 2

thenna
thenna

Reputation: 71

Find below the answer.

select * from empsal e where
3 =(select count(distinct empsal) from empsal where e.empsal<=empsal);

Replace '3' with '2' if you need 2nd max salary.

Upvotes: 0

Prasath Albert
Prasath Albert

Reputation: 1457

SELECT 
    p.firstname,
    p.lastname, 
    d.salary 
FROM 
    Persons p,
    Details d 
where 
    p.empid=d.empid  
order by 
    d.btID desc 
limit 2,1;

Upvotes: 0

Related Questions