Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary:

 Employee ID Salary 3 200 4 800 7 450

Write a SQL query to get the second highest salary from the table above. Also write a query to find the nth highest salary in SQL, where n can be any number.

The easiest way to start with a problem like this is to ask yourself a simpler question first. So, let’s ask ourselves how can we find the highest salary in a table? Well, you probably know that is actually really easy – we can just use the MAX aggregate function:

```select MAX(Salary) from Employee;
```

Remember that SQL is based on set theory

You should remember that SQL uses sets as the foundation for most of its queries. So, the question is how can we use set theory to find the 2nd highest salary in the table above? Think about it on your own for a bit – even if you do not remember much about sets, the answer is very easy to understand and something that you might be able to come up with on your own.

Figuring out the answer to find the 2nd highest salary

What if we try to exclude the highest salary value from the result set returned by the SQL that we run? If we remove the highest salary from a group of salary values, then we will have a new group of values whose highest salary is actually the 2nd highest in the original Employee table.

So, if we can somehow select the highest value from a result set that excludes the highest value, then we would actually be selecting the 2nd highest salary value. Think about that carefully and see if you can come up with the actual SQL yourself before you read the answer that we provide below. Here is a small hint to help you get started: you will have to use the “NOT IN” SQL operator.

Solution to finding the 2nd highest salary in SQL

Now, here is what the SQL will look like:

```SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )
```

Running the SQL above would return us “450”, which is of course the 2nd highest salary in the Employee table.

Subscribe to our newsletter for more free interview questions.

An explanation of the solution

The SQL above first finds the highest salary value in the Employee table using “(select MAX(Salary) from Employee)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest salary in the Employee table is 200,000 then that value will be excluded from the results using the “NOT IN” operator, and all values except for 200,000 will be retained in the results.

This now means that the highest value in this new result set will actually be the 2nd highest value in the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd highest Salary in the Employee table. And that is how the query above works.

An alternative solution using the not equals SQL operator

We can actually use the not equals operator – the “<>” – instead of the NOT IN operator as an alternative solution to this problem. This is what the SQL would look like:

```select MAX(Salary) from Employee
WHERE Salary <> (select MAX(Salary) from Employee )
```

How would you write a SQL query to find the Nth highest salary?

What we did above was write a query to find the 2nd highest Salary value in the Employee table. But, another commonly asked interview question is how can we use SQL to find the Nth highest salary, where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc? This is also an interesting question – try to come up with an answer yourself before reading the one below to see what you come up with.

The answer and explanation to finding the nth highest salary in SQL

Here we will present one possible answer to finding the nth highest salary first, and the explanation of that answer after since it’s actually easier to understand that way. Note that the first answer we present is actually not optimal from a performance standpoint since it uses a subquery, but we think that it will be interesting for you to learn about because you might just learn something new about SQL. If you want to see the more optimal solutions first, you can skip down to the sections that says “Find the nth highest salary without a subquery” instead.

The SQL below will give you the correct answer – but you will have to plug in an actual value for N of course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS:

```SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

```

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – pay special attention to the fact that “Emp1” appears in both the subquery (also known as an inner query) and the “outer” query. The outer query is just the part of the query that is not the subquery/inner query – both parts of the query are clearly labeled in the comments.

The subquery is a correlated subquery

The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it’s WHERE clause. In this case that value is the Emp1 table alias as we pointed out earlier. A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query. If you want to read more about the differences between correlated and uncorrelated subqueries you can go here: Correlated vs Uncorrelated Subqueries.

The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.

Finding nth highest salary example and explanation

Let’s step through an actual example to see how the query above will actually execute step by step. Suppose we are looking for the 2nd highest Salary value in our table above, so our N is 2. This means that the query will look like this:

```SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)
```

You can probably see that Emp1 and Emp2 are just aliases for the same Employee table – it’s like we just created 2 separate clones of the Employee table and gave them different names.

Understanding and visualizing how the query above works

Let’s assume that we are using this data:

 Employee ID Salary 3 200 4 800 7 450

For the sake of our explanation, let’s assume that N is 2 – so the query is trying to find the 2nd highest salary in the Employee table. The first thing that the query above does is process the very first row of the Employee table, which has an alias of Emp1.

The salary in the first row of the Employee table is 200. Because the subquery is correlated to the outer query through the alias Emp1, it means that when the first row is processed, the query will essentially look like this – note that all we did is replace Emp1.Salary with the value of 200:

```SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 200)
```

So, what exactly is happening when that first row is processed? Well, if you pay special attention to the subquery you will notice that it’s basically searching for the count of salary entries in the Employee table that are greater than 200. Basically, the subquery is trying to find how many salary entries are greater than 200. Then, that count of salary entries is checked to see if it equals 1 in the outer query, and if so then everything from that particular row in Emp1 will be returned.

Note that Emp1 and Emp2 are both aliases for the same table – Employee. Emp2 is only being used in the subquery to compare all the salary values to the current salary value chosen in Emp1. This allows us to find the number of salary entries (the count) that are greater than 200. And if this number is equal to N-1 (which is 1 in our case) then we know that we have a winner – and that we have found our answer.

But, it’s clear that the subquery will return a 2 when Emp1.Salary is 200, because there are clearly 2 salaries greater than 200 in the Employee table. And since 2 is not equal to 1, the salary of 200 will clearly not be returned.

So, what happens next? Well, the SQL processor will move on to the next row which is 800, and the resulting query looks like this:

```SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 800)
```

Since there are no salaries greater than 800, the query will move on to the last row and will of course find the answer as 450. This is because 800 is greater than 450, and the count will be 1. More precisely, the entire row with the desired salary would be returned, and this is what it would look like:

```EmployeeID Salary
7 450

```

It’s also worth pointing out that the reason DISTINCT is used in the query above is because there may be duplicate salary values in the table. In that scenario, we only want to count repeated salaries just once, which is exactly why we use the DISTINCT operator.

A high level summary of how the query works

Let’s go through a high level summary of how someone would have come up with the SQL in the first place – since we showed you the answer first without really going through the thought process one would use to arrive at that answer.

Think of it this way – we are looking for a pattern that will lead us to the answer. One way to look at it is that the 2nd highest salary would have just one salary that is greater than it. The 4th highest salary would have 3 salaries that are greater than it. In more general terms, in order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself. And that is exactly what the query above accomplishes – it simply finds the salary that has N-1 salaries greater than itself and returns that value as the answer.

Find the nth highest salary using the TOP keyword in SQL Server

We can also use the TOP keyword (for databases that support the TOP keyword, like SQL Server) to find the nth highest salary. Here is some fairly simply SQL that would help us do that:

```SELECT TOP 1 Salary
FROM (
SELECT DISTINCT TOP N Salary
FROM Employee
ORDER BY Salary DESC
) AS Emp
ORDER BY Salary
```

To understand the query above, first look at the subquery, which simply finds the N highest salaries in the Employee table and arranges them in descending order. Then, the outer query will actually rearrange those values in ascending order, which is what the very last line “ORDER BY Salary” does, because of the fact that the ORDER BY Default is to sort values in ASCENDING order. Finally, that means the Nth highest salary will be at the top of the list of salaries, which means we just want the first row, which is exactly what “SELECT TOP 1 Salary” will do for us!

Find the nth highest salary without using the TOP keyword

There are many other solutions to finding the nth highest salary that do not need to use the TOP keyword, one of which we already went over. Keep reading for more solutions.

Find the nth highest salary in SQL without a subquery

The solution we gave above actually does not do well from a performance standpoint. This is because the use of the subquery can really slow down the query. With that in mind, let’s go through some different solutions to this problem for different database vendors. Because each database vendor (whether it’s MySQL, Oracle, or SQL Server) has a different SQL syntax and functions, we will go through solutions for specific vendors. But keep in mind that the solution presented above using a subquery should work across different database vendors.

Find the nth highest salary in MySQL

In MySQL, we can just use the LIMIT clause along with an offset to find the nth highest salary. If that doesn’t make sense take a look at the MySQL-specific SQL to see how we can do this:

```SELECT Salary FROM Employee
ORDER BY Salary DESC LIMIT n-1,1
```

Note that the DESC used in the query above simply arranges the salaries in descending order – so from highest salary to lowest. Then, the key part of the query to pay attention to is the “LIMIT N-1, 1”. The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. So, it’s saying that the offset of the first row to return should be N-1, and the max number of rows to return is 1. What exactly is the offset? Well, the offset is just a numerical value that represents the number of rows from the very first row, and since the rows are arranged in descending order we know that the row at an offset of N-1 will contain the (N-1)th highest salary.

Find the nth highest salary in SQL Server

In SQL Server, there is no such thing as a LIMIT clause. But, we can still use the offset to find the nth highest salary without using a subquery – just like the solution we gave above in MySQL syntax. But, the SQL Server syntax will be a bit different. Here is what it would look like:

```SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY
```

Note that I haven’t personally tested the SQL above, and I believe that it will only work in SQL Server 2012 and up. Let me know in the comments if you notice anything else about the query.

Find the nth highest salary in Oracle using rownum

Oracle syntax doesn’t support using an offset like MySQL and SQL Server, but we can actually use the row_number analytic function in Oracle to solve this problem. Here is what the Oracle-specific SQL would look like to find the nth highest salary:

```select * from (
select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n;  /*n is nth highest salary*/
```

The first thing you should notice in the query above is that inside the subquery the salaries are arranged in descending order. Then, the row_number analytic function is applied against the list of descending salaries. Applying the row_number function against the list of descending salaries means that each row will be assigned a row number starting from 1. And since the rows are arranged in descending order the row with the highest salary will have a 1 for the row number. Note that the row number is given the alias rownumb in the SQL above.

This means that in order to find the 3rd or 4th highest salary we simply look for the 3rd or 4th row. The query above will then compare the rownumb to n, and if they are equal will return everything in that row. And that will be our answer!

Find the nth highest salary in Oracle using RANK

Oracle also provides a RANK function that just assigns a ranking numeric value (with 1 being the highest) for some sorted values. So, we can use this SQL in Oracle to find the nth highest salary using the RANK function:

```select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;
```

The rank function will assign a ranking to each row starting from 1. This query is actually quite similar to the one where we used the row_number() analytic function, and works in the same way as well.

We’ve now gone through many different solutions in different database vendors like Oracle, MySQL, and SQL Server. Hopefully now you understand how to solve a problem like this, and you have improved your SQL skills in the process! Be sure to leave a comment if you have any questions or observations.

Subscribe to our newsletter for more free interview questions.

• SHEKHAR BHANDAKKAR

When using the rank() what if multiple employees have same salary, will it still return the Nth highest salary ?

• Prashant Ingle

ONLY SELECTED 3RD HIGH SALARY IN EMPLOYEES TABLE

select salary,last_name,first_name from
(
select last_name,first_name,salary,
DENSE_RANK() over (order by Salary DESC) RN
from Employees
)
where RN=3

• Anil Vighne

Vary important information and nice explanation ………!!!
Thank You

• Purva Raut

can we write select max(salary) from table_name to find the maximum salary

• Darshan

CREATE TABLE #Temp_Salary
(
SALARY Decimal(10,2),
EMPNAME VARCHAR(50)
)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10000,’ABC’)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(12000,’ABC2′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10300,’AB3′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(1400,’ABC4′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(15000,’ABC5′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(16000,’ABC6′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10050,’AB7′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10300,’ABC8′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(17000,’ABC9′)
INSERT INTO #Temp_Salary(SALARY,EMPNAME) VALUES(10400,’ABC10′)

SELECT * FROM #Temp_Salary ORDER BY SALARY DESC

SELECT SALARY,EMPNAME FROM #Temp_Salary
ORDER BY SALARY
DESC OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY

DROP TABLE #Temp_Salary

• Elangovan P

Awesome explanation oracle query.

• Cognize Stuff

i am getting incorrect result. i have duplicate records. please check:

mysql> SELECT *
-> FROM emp Emp1
-> WHERE 1 = (
-> SELECT COUNT(DISTINCT Emp2.sal)
-> FROM emp Emp2
-> WHERE Emp2.sal > Emp1.sal);
+—–+——+
| eid | sal |
+—–+——+
| 23 | 4400 |
| 33 | 4400 |
| 44 | 6400 |
+—–+——+
3 rows in set (0.00 sec)

mysql> select * from emp;
+—–+——+
| eid | sal |
+—–+——+
| 1 | 400 |
| 2 | 4300 |
| 4 | 300 |
| 22 | 6400 |
| 23 | 4400 |
| 33 | 4400 |
| 44 | 6400 |
+—–+——+
7 rows in set (0.00 sec)

• piyush kaushik

why can’t we use TOP in oracle…

• swathi vullangi

nice 1

• Ajit Patil

select first_name, salary from (select rownum as rn, first_name, salary from employees order by salary desc) where rn=4;

• garima arora

Superb explanation..thanks.

• PJ

Thanks so much for the explanation of the 2nd highest salary query execution. I searched many websites for it, but your explanation is composed of simple and easy to understand words and style.

• Ashiqur Rahman Opu

Find TOP 2 max salary from this table?

• Vikash

SELECT Salary FROM Employee
ORDER BY Salary DESC LIMIT n-1,1
does not work with:
idnamesalary Edit Copy Delete1Ram20000 Edit Copy Delete2shayam20000 Edit Copy Delete3Reeta25000 Edit Copy Delete4sheeta18000 Edit Copy Delete5Geeta18000 Edit Copy Delete6Neeta25000

• Really nice design and good content, nothing at all else we need :D.

• Anonymous

Very Well Explained.
Thanks a lot

• Dhwanit Shah

Without using sub query !!!

1) With duplicate salaries:

select b.salary,count(b.salary) as ranking from (select distinct(salary) from employee) a, (select distinct(salary) from employee) b where b.salary <= a.salary group by b.salary where ranking = n /*nth required rank*/

2) Without duplicate salaries

select b.salary,count(b.salary) as ranking from (employee) a, (employee) b where b.salary <= a.salary group by b.salary where ranking = n /* nth required rank*/

This will fetch the ranks for each of the different salaries and then filter them against the required rank.

• Praveen Pandey

SELECT *

FROM employee Emp1

WHERE (4) = (

SELECT COUNT(DISTINCT(Emp2.salary))

FROM employee Emp2

WHERE Emp2.salary<= Emp1.salary)

• 123

WTF!

• SQL learner

really useful !!

• guest

grt job

• Hitesh

Find n’th highest salary in sql server
http://www.codesolution.org/?p=189

• Puneet Kumar

select * from (select * from testsalary order by 2 desc limit n)x offset (n-1)

• Sahil Chopara

use then dense_rank() analytic function.

• Haritha Green

I think it works fine when it is ‘N’ other than ‘N-1’ !

• shafi

Try This One 🙂
SELECT salary FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee) ORDER BY salary DESC LIMIT 1

• psm

Thanks for explaining this query. in Oracle I have a table having 2 persons with same salary and when i am using query with the rank function , then output is like

empno salary ranking
7839 5000 1

7902 3000 2

7788 3000 2

7566 2975 4

My question is if i want to find 3rd max salary ,then will rank function will work in this case?

• partha Pratim

thanks !! it worked ..

• partha Pratim

hi , I have two tables 1>customer 2>order .. now how can i get the 2nd highest order total , any help !!

• Sanjay Kumar Bhargava

very nice article and useful also, thanks for it. I am failed in an interview when the question was asked to me but now I can solve all these types query so really thanks

• Shailendra Singhai

Select min(sal) from (select sal from emp_table group by sal order by sal desc fetch first n row only) i feel best result in term of performance…here no need to create alias and any comparision

• 🙂 It will definitely…

• pritam

With Denserank..we are able to fetch 5 the highest sale…it is working fine.

• sabarish

best one, works perfect , thanks for sharing.

• Jnanendra Mohanty

There is another way to get it by using PSEUDO Columns “LEVEL”

For Nth Maximum Salary

SELECT LEVEL, MAX(Sal)
FROM Emp
WHERE LEVEL = &Nth
CONNECT BY PRIOR Sal>Sal
GROUP BY LEVEL;

For Nth Minimum Salary

SELECT LEVEL, MIN(Sal)
FROM Emp
WHERE LEVEL = &Nth
CONNECT BY PRIOR Sal<Sal
GROUP BY LEVEL;

• Yalin Meric, Logica IT

Just replace n with the order of largest salary you want to find

select top 1 salary from employee where salary < (select min(salary) from (select top n-1 salary from employee order by salary desc) as tmp) order by salary desc

• PUSHKAR

Find the nth highest salary in MySQL:
In this we should use DISTINCT.

• kajam

Its very use full for all of us thanks for this

• G1

awesome. the very easy way to learn

• Abhaya

Hi guys
No doubt I’m using the dense_rank function to get the nth highest/lowest value from a table. But I got a simple sol’n for this. please have a look on below method…

select emp1.sal from emp emp1
where 2 =( select count(distinct( emp2.sal )) from emp emp2 where emp2.sal>emp1.sal)

—–This is to fetch the 3rd highest salary from emp table in oracle DB.

So my question is from the above two which one is better on performance point of view.

looking forward to hear from you guys.

• Abhaya

Awesome yaar

• Abhaya

Hi Ashvini,
I haven’t looked into ur comment But just wondering for 3rd highest as there is some demerit of rank function.

Thanks for showing it (e.g dense_rank())

• Julian Mojico

you are the best buddy; i have been looking the easy way to do this during 2 days

• I got the solution:

Multiple Sql server queries to get second,third,fourth or nth highest/maximum salary from employee table

http://www.webcodeexpert.com/2014/08/sql-server-query-to-get.html

• cromateu

Usually the question is asked to write without using subquery.
In that case,we should use either limit cause or offset N-1 right?

• Use of row_number() and rank() gives wrong answer if there exists duplicate record in table…

For Example:-

Suppose in salary column first two of them have same salary 3000 and remaining employee having less then that ..

i.e

A-3000

B-3000

C-2500

and so on…

if suppose i use row_number() then even if for condition n=1 or n=2 it will get same answer…. even if i asking for 1st and 2nd max salary…
o/p is 3000 in both case..

If suppose i use rank() then due to duplication A and B will share rank 1 and C will get rank 3 directly , hence when we will give condition like n=2 it will give no data but required out come should be 2500 and outcome is 2500 when we give condition n=3 as rank would be assign to salary 2500…

Hence solution for above problem is….

select * FROM (
select EmployeeID, Salary
,dense_rank() over (order by Salary DESC) ranking
from Employee
)
WHERE ranking = N;

same query only use dense_rank() instead rank() it would also give outcome for duplicate data to….
as dense_rank share rank but not skip rank…. gives consecutive ranks

• RATHORE

OFFSET QUERY IS NOT WORKING EVEN IN SQL SERVER 2012 AND ABOVE

• Kavi

Great Explanation!! Thumbs Up!!

• Karthikaiselvan

Instead N-1 we can have >= in subquery, which like as follows
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE N = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary >= Emp1.Salary)

• താപ്പാന

add distinct – select min(salary) from Employee where salary in (select distinct TOP N salary from Employee order by salary desc)

• Sam

very good explaination…Thanks a lot

Corrections in the example given in the article needed.

Incorrect form (as above in the article):

SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST ROW ONLY

Correct form:

SELECT Salary FROM Employee
ORDER BY Salary DESC OFFSET N-1 ROW(S)
FETCH FIRST 1 ROW ONLY

• rahat

it work in sql server

Wow man wow what a stupendus fantabulous one man.. truly you rock!!!

• RAGHU

Thanks a lot for the good explanation, really you made it very easy to understand

• Krishna Vedulla

it’s the gr8 explanation… of sql.. i found it vey useful… n awesome tutorial

• Aj

SELECT * FROM Employee Emp1 WHERE (1) = ( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > 450);

If the above query works fine then why don’t the below query run fine?

SELECT * FROM Employee Emp1 WHERE (1) = (1);

{
( SELECT COUNT(DISTINCT(Emp2.Salary)) FROM Employee Emp2 WHERE Emp2.Salary > 450);

this will anyways give 1 as the output.
}

• AkG

This will fail in case where all top N have the same salary or n/2 have same salary and other n/2 have same salary and so on.

• vijay bhatt

You can refer following :

• basayya

SELECT distinct salary from employee order by salary desc limit 1

• sac

for 2nd max:
select max(salary) from employee
where salary < ( select max(salary) from employee );

so on for nth max
select max(salary)from employee
where salary < (… n-1 times …)

• Kris

SELECT SAL
FROM
(SELECT SAL, DENSE_RANK() OVER (ORDER BY SAL DESC) MAX_SALARY FROM EMPLOYEES) B
WHERE B.MAX_SALARY=2

1)select * from emp order by sal desc;

7839 KING PRESIDENT 17-NOV-81 6000
7902 FORD ANALYST 7566 03-DEC-81 3600
7788 SCOTT ANALYST 7566 19-APR-87 3600
7566 JONES MANAGER 7839 02-APR-81 3570

2)sELECT emp.*,rank() over (order by sal desc) from emp;

7839 KING PRESIDENT 17-NOV-81 6000 1
7902 FORD ANALYST 03-DEC-81 3600 2
7788 SCOTT ANALYST 19-APR-87 3600 2
7566 JONES MANAGER 02-APR-81 3570 4

lst column shows rank…so if u r looking for 3rd highest salary holder,it will not give answer so u should use dense_rank() …

SELECT emp.*,dense_rank() over (order by sal desc)r from emp;

7839 KING PRESIDENT 17-NOV-81 6000 1
7902 FORD ANALYST 03-DEC-81 3600 2
7788 SCOTT ANALYST 19-APR-87 3600 2
7566 JONES MANAGER 02-APR-81 3570 3
7698 LUTHER MANAGER 01-MAY-81 3420 4

• Raj

nth lowest is incorrect.

• sac

i think this too work..correct me if i m wrong
select max(salary) from employee
where salary < ( select max(salary) from employee )

• Kulal

Super.. Thank you.

• Naveenkumar

But this doesn't work in oracle

• Srinivas

Hi Abishek I Think This query will Satisfy U R Answer
SELECT * FROM(
SELECT E.*,DENSE_RANK() OVER(ORDER BY SAL DESC) AS RANK FROM EMP E)
WHERE
RANK IN(1,3)

• abhishek

can we select two sal at a time like 1st & 3rd highest sal or 4th & 8th highest salary randomly it will be great help if some one can…….

• varoon10

Thanks Sanchari!

• varoon10

No prob! Thanks Preeti!

• varoon10

Thanks Vinay!

• varoon10

Thanks Fida!

• varoon10

• Vinay

Nice article…

• +91 9560523261

first a fall join both table then

if u got in one column in Both Salary then Write Query
:-
Select top(1) * from (Select top(9) * from Table1 order by Salary desc) alias_name(e) order by Salary asc

I hope ur problem is solved by this answer

You can call me related any Sql query.

• try kar raha tha

gghhghgj bk

• Fida Husain

Good Very good

• pooja

This is the first time I have understood the query involving 1=count.
Thanks a ton.

• Shree

Too good ..appreciated..well done 🙂

• pankaj kumar jha

select max(salary) as max_salary from employee where salary !=(select max(salary) from empolyee)

• Vinodhini

this one seems to work. let me know if there is any problem with it.

select * from
(select sal,row_number() over(order by sal desc) as R from
(select distinct sal
from emp)) where R=n;

• Vinodhini

Hi,
select * from (
select Emp.*,
row_number() over (order by Salary DESC) rownumb
from Employee Emp
)
where rownumb = n; /*n is nth highest salary*/
in this solution can you let me know the changes to be done in order to apply for distinct salary?
In short, how to apply row_number() for distinct values?

• Divya

Nice

• Gaurav Sharma

SELECT ID,Name,salary, RANK() OVER (ORDER BY salary DESC) AS test_rownum

FROM Salesperson

QUALIFY test_rownum=2

• Gaurav Sharma

Best Answer I have ever seen for This Question.

• Ritesh Sharma

Get nth highest salary

select min(salary) from employee where salary in (select top n distinct salary from employee order by salary desc)

Get nth lowest salary

select max(salary) from employee where salary in (select top n distinct salary from employee order by salary)

Happy Coding 🙂

• Jesse

This isnt really a good solution but then again, thiis isnt a good questin to begin with either.. One would always ask to LIST the employee (along with his salary) who earns the nth highest salalry. See my point?

• Preeti

it was really very helful. Thanks a lot 🙂

• Nishu

We have two tables Employee table and Department table. Find 9th Highest salary. Anyone Please tell the query.

• ankit jaurihar

with ref to oracle ….. select max(salary) from

(select salary, last_name from

(select salary, last_name from employees order by salary desc)

where rownum <= &d)

this will give top nth salary; using the sub query concept.

Also want to clarify – there is no LIMIT clause in SQL Server, as discussed above.

SELECT salary
FROM (SELECT salary FROM salary Order BY salary DESC LIMIT n) as x
Order BY salary ASC
LIMIT 1

• varoon10

Nice one! Thanks for sharing Vivek!

I have a simple query instead of this to fetch nth highest which is develope by me…

Nth Max salary –

select min(salary) from employee where salary in (select TOP N salary from employee order by salary desc)

Nth Min salary –

select max(salary) from employee where salary in (select TOP N salary from employee order by salary asc)

• varoon10

Yes Deepesh, thanks. that syntax works in MySql, but is different in SQL Server and Oracle. i've updated the page to show some more efficient solutions as well.

• ymahalakshmi

Nice explanation. Great.

• Vijay

Suppose, if i have two rows with the value of 800. It will fail!!

• Vijay

This will fail if multiple rows of higher salary value is present.

• Sanchari Paul

superb….it is logically absolutely right

• kfcinemas

'll this works on oracle??

• priyal

yes this is also correct

• Can't i use this?
SELECT salary FROM employee ORDER BY salary DESC LIMIT n-1,1