Get Second, Third, Forth or nth Largest Salery or Value of a Collumn in Mysql or Sql

This is a very common interview question that will ask to you almost every interview like how you will get the Second largest salery, Third largest salery or ........ nth largest salery.

Here is a simple query that i want to show you all to get second largest salery from table. Suppose your table name is tbl_user and you want to find out the second largest salery (35000) from below example -

Table Name : tbl_user :

+-------------+----------------+-----------------+-----------------+
|          id |   name         | salery          | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Salman         | 30000           | 2007-05-21      |
|           2 | Abul Barkat    | 35000           | 2007-05-21      |
|           3 | Rambir Singh   | 40000           | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+

Query To Get Second largest salery:

 "SELECT  MAX(Salery) FROM tbl_user WHERE  salery NOT IN (SELECT  MAX(Salery) FROM tbl_user)" ;

Above query will return you second largest salery(35000)  from the above table.

Now you want to find out the nth largest salery from the below
example table:

Above query will only work to find the second largest salery but suppose if they ask you to find 3rd largest salery or 4th largest salery or nth largest salery then ? 

See the below example-

Table Name : tbl_user

+-------------+----------------+-----------------+-----------------+
|          id |   name         | salery          | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | Salman         | 30000           | 2007-05-02      |
|           2 | Abul Barkat    | 35000           | 2007-05-23      |
|           3 | Rambir Singh   | 40000           | 2007-05-21      |
|           4 | Anil Kumar     | 42000           | 2007-05-27      | 

+-------------+----------------+-----------------+-----------------+

Query To Get Nth largest salery from table:

 "SELECT * FROM tbl_user AS usr WHERE (SELECT COUNT(*) FROM tbl_user WHERE salery > usr.salery ) = nth " ;

In the above query nth is the value like 0, 1, 2 ........nth

Suppose you want to find out the Second largest saley the nth = 1
If you want to find out the Third largest value then the nth = 2
If you want to find out the Forth largest value then the nth = 3 
If you want to find out the Fifth largest value then the nth = 4
-
-
-
If you want to find out the nth largest value then the nth = n-1  


If you like this post please don't forgot to notify me to post a comment.

Happy coding guys :)