Home » SQL & PL/SQL » SQL & PL/SQL » Fetching 2nd or 3rd largest value from a table
Fetching 2nd or 3rd largest value from a table [message #7104] Wed, 21 May 2003 06:48 Go to next message
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 #7110 is a reply to message #7104] Wed, 21 May 2003 07:51 Go to previous messageGo to next message
Madhu
Messages: 36
Registered: April 2001
Member
Here it is,

select level,max(col name) from table_name
where level = &n
connect by prior col name > col name
group by level;
Re: Fetching 2nd or 3rd largest value from a table [message #7111 is a reply to message #7110] Wed, 21 May 2003 10:57 Go to previous messageGo to next message
freewill
Messages: 16
Registered: October 2002
Junior Member
u can use subquery.
select sub.empno
from (select empno,salary
from emp
order by salary desc) sub
where rownum=&n
hope this helps!
Re: Fetching 2nd or 3rd largest value from a table [message #7113 is a reply to message #7111] Wed, 21 May 2003 13:55 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: POSITIONED DELETE(CURRENT OF)
Next Topic: Valid date ranges
Goto Forum:
  


Current Time: Fri Mar 29 03:20:20 CDT 2024