1. SELECT TOP 3 salary FROM employees WHERE job_desc = ‘Sr. Developer’
2. SELECT TOP 3 salary FROM employees WHERE job_desc = ‘Sr. Developer’;
3. SELECT TOP 3 salary FROM employees WHERE job_desc = ‘Sr. Developer’;
4. SELECT TOP 3 salary FROM employees WHERE job_desc = ‘Programmer’;

Find 3rd Highest Salary In Sql W3schools

I need to write a query that will return the third highest salaried employee in the company.

I was trying to accomplish this with subqueries, but could not get the answer. My attempts are below:

select Max(salary)
from employees
where Salary not in

 (select Max(salary)
from employees
where Salary not in 

(select Max(salary)
from employees));

My thought was that I could use 2 subqueries to elimitate the first and second highest salaries. Then I could simply select the MAX() salary that is remaining. Is this a good option, or is there a better way to achieve this?

sql

ShareFollow

edited Dec 19, 2013 at 20:27

asked Dec 19, 2013 at 20:02

23711 gold badge22 silver badges55 bronze badges

• 3Welcome to Stack Overflow! Please specify the RDBMS that you are targeting by adding the appropriate tag (Oracle, SQL Server, MySQL, etc.). There may be answers that take advantage of language or product features that are not universally supported. Also, by tagging it with a specific RDBMS, your question may receive attention from people better suited to answer it. – Taryn Dec 19, 2013 at 20:04
• i am a novice please sum1 explain how limit works and please answer my question how many subquery chain is permitted? – Prateek Chaudhary Dec 19, 2013 at 20:12
• I don’t see any SUM calls in there. The only aggregation is MAX. Did you copy the entirety of your query? – billinkc Dec 19, 2013 at 20:14
• 2Which database are you using? – Robert Harvey Dec 19, 2013 at 20:58
• 1Possible duplicate of SQL query to find Nth highest salary from a salary table – LittleBobbyTables – Au Revoir Aug 1, 2019 at 15:22

Show 2 more comments

32 Answers

Sorted by:Trending sort available 

Introducing: Trending sort 

You can now choose to sort by Trending, which boosts votes that have happened recently, helping to surface more up-to-date answers.

Trending is based off of the highest score sort and falls back to it if no posts are trending.Try itDismiss                                              Highest score (default)                                                                   Trending (recent votes count more)                                                                   Date modified (newest first)                                                                   Date created (oldest first)                              

1

2Next

53

The most simple way that should work in any database is to do following:

SELECT * FROM `employee` ORDER BY `salary` DESC LIMIT 1 OFFSET 2;

Which orders employees by salary and then tells db to return a single result (1 in LIMIT) counting from third row in result set (2 in OFFSET). It may be OFFSET 3 if your DB counts result rows from 1 and not from 0.

This example should work in MySQL and PostgreSQL.

ShareFollow

edited Dec 19, 2013 at 20:54

Mike Szyndel

10.1k77 gold badges4444 silver badges6161 bronze badges

answered Dec 19, 2013 at 20:05

Orel Eraki

11.5k33 gold badges2727 silver badges3434 bronze badges

Add a comment

https://c7d12ac43490b65281be281d6860baff.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlReport this ad

23

You can get the third highest salary by using limit , by using TOP keyword and sub-query

1. TOP keywordSELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM Table_Name ORDER BY salary DESC) AS Comp ORDER BY salary ASC
2. limitSELECT salary FROM Table_Name ORDER BY salary DESC LIMIT 2, 1
3. by subquerySELECT salary FROM (SELECT salary FROM Table_Name ORDER BY salary DESC LIMIT 3) AS Comp ORDER BY salary LIMIT 1;

I think anyone of these help you.

ShareFollow

edited Feb 27, 2016 at 21:22

marc_s

706k168168 gold badges13051305 silver badges14261426 bronze badges

answered Apr 21, 2015 at 13:36

sandeep sharma

46133 silver badges99 bronze badges

Add a comment

12

You may try (if MySQL):

SELECT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;

This query returns one row after skipping two rows.

You may also want to return distinct salary. For example, if you have 20,20,10 and 5 then 5 is the third highest salary. To do so, add DISTINCT to the above query:

SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 2, 1;