Fetching 2nd or 3rd largest value from a table [message #7104] |
Wed, 21 May 2003 06:48 |
Vineet
Messages: 10 Registered: September 2000
|
Junior Member |
|
|
Can u send me the generalized query to fetch 2nd largest , 3rd largest or 4th largest value
in a table ,
eg. in emp table if i want to get an employee no.
who gets 3rd highest salary !!!
thanks in advance
Vineet
|
|
|
|
|
Re: Fetching 2nd or 3rd largest value from a table [message #7113 is a reply to message #7111] |
Wed, 21 May 2003 13:55 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Sorry, but this query will not work. You can never test for ROWNUM equal to a number higher than 1.
The best way is to use an analytical function:
select empno
from (select empno, dense_rank() over (order by sal desc) rank
from emp)
where rank = 2;
Otherwise, you have to use ROWNUM, but note that it may handle ties (same salary) differently than you expect:
select empno
from (select t.empno, rownum rn
from (select empno
from emp
order by sal desc) t)
where rn = 3;
Compare these two queries with the stock EMP table and vary the value between 2 and 3. Note the differences in results.
|
|
|
Re: Fetching 2nd or 3rd largest value from a table [message #7118 is a reply to message #7104] |
Wed, 21 May 2003 22:48 |
Ashish Mehta
Messages: 5 Registered: September 2002
|
Junior Member |
|
|
select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;
Example :
-- Given a table called emp with the following columns:
-- id number
name varchar2(20)
sal number
For the second highest salary:
select level, max(sal) from emp
where level=2
connect by prior sal > sal
group by level
|
|
|