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:
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.
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.
This article is contributed by Rishav Shandilya. If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
- How to print duplicate rows in a table?
- SQL | Create Table Extension
- PHP | MySQL ( Creating Table )
- Check if Table, View, Trigger, etc present in Oracle
- How to Get the names of the table in SQL
- Find the factorial of a number in pl/sql
- Find the area and perimeter of right triangle in PL/SQL
- SQL | Checking Existing Constraints on a Table using Data Dictionaries
- Program to find Simple Interest and Compound Interest in PL/SQL
- MySQL | Recursive CTE (Common Table Expressions)
- Query to find 2nd largest value in a column in Table
- SELECT INTO statement in SQL
- Pivot and Unpivot in SQL
- Common error in Group By
Improved By : Shahriar Nazim ReaL