Home » questions » how to find nth greatest value using max command in SQL?

how to find nth greatest value using max command in SQL?

3
2006-08-04 09:02:49, Category: Programming & Design
can u slove my problem..any SQL user can slove it hey if i have ten records in my salary coloumn and i want to know the salary of the 5th greatest one.but use the subquery command take lot of time (select from max(sal) from emp where sal<(select from max(sal) from emp) writng this 5 times will take lot of time can any 1 tell me how to find it in a better ways using co-realted query(finding by nth place from which i can find directly 5th maximum value) please help me

Answers

  1. jsd115

    On 2006-08-04 09:18:38


    Here's a way to do it in Oracle: select * from (select sal from emp order by sal desc) where rownum=5; And for MySQL: select sal as salary from emp order by sal desc limit 5,1; Explanation: The query generates a list of all salaries and orders them from highest to lowest. It then chooses the 5th row which will contain the 5th highest salary.

  2. Raj Sarma

    On 2006-08-04 09:22:42


    select max(sal) from (select top 5 sal from emp) b

  3. slowman011

    On 2006-08-04 15:24:56


    if your SQL supports derived tables, like SQL-Server, you can use the following. Get the top n records in the derived table (top5 here), and then get the last of it by reversing the order in the outer select. SELECT TOP 1 sal FROM (SELECT TOP 5 sal FROM salary ORDER BY sal ASC) top5 ORDER BY sal DESC