With a median salary of $81,800, SQL Server database administrators are the third highest paid members of the database administration field. In fact, they’re more highly compensated than IT professionals in almost every other sector, including network security technicians, who earn a median salary of $78,000 per year. In contrast to many other IT occupations, database administrators’ pay is fairly comparable across different industries

Display 3rd Highest Salary In Sql

19

9
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
Share
Improve this question
Follow
edited Dec 19, 2013 at 20:27
asked Dec 19, 2013 at 20:02
user avatar
Prateek Chaudhary
22711 gold badge22 silver badges55 bronze badges
3
Welcome 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
2
Which database are you using? –
Robert Harvey
Dec 19, 2013 at 20:58
1
Possible 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:

Highest score (default)
1
2
Next

51

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.

Share
Improve this answer
Follow
edited Dec 19, 2013 at 20:54
user avatar
Mike Szyndel
10.1k77 gold badges4444 silver badges6161 bronze badges
answered Dec 19, 2013 at 20:05
user avatar
Orel Eraki
11.4k33 gold badges2727 silver badges3434 bronze badges
Add a comment

23

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

TOP keyword

SELECT TOP 1 salary
FROM
(SELECT TOP 3 salary
FROM Table_Name
ORDER BY salary DESC) AS Comp
ORDER BY salary ASC
limit

SELECT salary
FROM Table_Name
ORDER BY salary DESC
LIMIT 2, 1
by subquery

SELECT salary
FROM
(SELECT salary
FROM Table_Name
ORDER BY salary DESC
LIMIT 3) AS Comp
ORDER BY salary
LIMIT 1;

Introduction

In this article, we will discuss on how to find the nth highest salary from the table. Here is a description of the problem: Write a SQL query to get the nth highest salary from the Employee table. We need to create a query statement that will find out the 3rd highest salary form the employee table. Below is given employee table:

WITH t AS (SELECT Salary, dense_rank() OVER(ORDER BY Salary DESC) rn FROM Employee) SELECT Salary FROM t WHERE rn = 3;

The dense_rank() function is a MySQL function that ranks the results in an order you specify. The syntax of this function is as follows:

SELECT dense_rank() over(order by ) FROM

For example, if we wanted to find the 3rd highest salary in our Employee table, we would use WITH t AS (SELECT Salary, dense_rank() OVER(ORDER BY Salary DESC) rn FROM Employee) SELECT Salary FROM t WHERE rn = 3;

Use dense_rank() function

use the dense_rank() function to return the rank of the row within a partition. For example, assume that you have a table called Employee with columns EmployeeID, Name and Salary, and there are three rows: 1. John Smith 2. Jane Doe 3. Sam Brown

The following query returns the salary range of each employee:

SELECT DENSE_RANK() OVER (PARTITION BY EmployeeID ORDER BY Salary DESC) AS [Employee ID Rank] ,* FROM Employee

Conclusion

This article provides detailed information on how to get the 3rd highest salary in SQL.