Finding Nth highest salary in a table is the most common question asked in interviews. Here is a way to do this task using dense_rank() function.

Consider the following table:

**Employee**

ename |
sal |

A | 23000 |

B | 31000 |

C | 24500 |

D | 35000 |

E | 28500 |

F | 31500 |

G | 39800 |

H | 51000 |

I | 39800 |

**Query :**

select * from( select ename, sal, dense_rank() over(order by sal desc)r from Employee) where r=&n; To find to the 2nd highest sal set n = 2 To find 3rd highest sal set n = 3 and so on.

**Output :**

**DENSE_RANK :**

1. DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

2. This function accepts arguments as any numeric data type and returns NUMBER.

3. As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.

4. In the above query the rank is returned based on sal of the employee table. In case of tie, it assigns equal rank to all the rows.

**Alternate Solution : **

—————————————————————————————————————————————————————————————————————–

CREATE TABLE `Employee` (

`ENAME` varchar(225) COLLATE utf8_unicode_ci NOT NULL,

`SAL` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`ENAME`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

————————————————————————————————————————————————————-

6th highest mysql> select * from ((select * from Employee ORDER BY `sal` DESC limit 6 ) AS T) ORDER BY T.`sal` ASC limit 1; Alternate use of Limit: select * from Employee ORDER BY `sal` DESC limit 5,1; // will return 6th highest +-------+-----+ | ENAME | SAL | +-------+-----+ | B | 300 | +-------+-----+ 1 row in set (0.00 sec)

——————————————————————————————————————————————————–

mysql> select * from Employee; +-------+-----+ | ENAME | SAL | +-------+-----+ | A | 100 | | B | 300 | | C | 200 | | D | 500 | | F | 400 | | G | 600 | | H | 700 | | I | 800 | +-------+-----+ 8 rows in set (0.00 sec)

Thanks to **Vijay** for suggesting this alternate solution.

## leave a comment

## 0 Comments