OraFAQ Forum: SQL & PL/SQL » Finding the Second largest value from a column

Home » SQL & PL/SQL » SQL & PL/SQL » Finding the Second largest value from a column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Finding the Second largest value from a column [message #20090]

Mon, 29 April 2002 22:08

Prads
Messages: 5
Registered: April 2002

Junior Member

Hi everybody,

Please tell me how can I find the Second - Largest value [[which is of number datatype]] from a column using simple sql query.

Thanks & Regards,
Prads

Report message to a moderator

Re: Finding the Second largest value from a column [message #20091 is a reply to message #20090]

Tue, 30 April 2002 00:21

Anders Mattsson
Messages: 1
Registered: April 2002

Junior Member

SELECT MIN( iCOLUMN ) FROM tTable
WHERE rownum <= 2
ORDER BY iCOLUMN

Report message to a moderator

Re: Finding the Second largest value from a column [message #20093 is a reply to message #20090]

Tue, 30 April 2002 00:41

shailendra
Messages: 8
Registered: January 2002

Junior Member

select distinct sal form emp a where 1 = select count(distinct sal) from emp b where b.sal>a.sal
this query canbe generlised. if u want 5th max then put 4 in place of 1..

Report message to a moderator

Re: Finding the Second largest value from a column [message #20095 is a reply to message #20090]

Tue, 30 April 2002 01:23

Shankar. V
Messages: 5
Registered: April 2002

Junior Member

Prad,

Replace col_name and my_table with appropriate column and table name.

select level, max('col_name') from my_table
where level = 2
connect by prior ('col_name') > 'col_name')
group by level;

Report message to a moderator

Re: Finding the Second largest value from a column [message #20136 is a reply to message #20090]

Tue, 30 April 2002 13:16

dug
Messages: 7
Registered: March 2002

Junior Member

use a rank() function to get the second largest value in a column

select eno,ename,esal from (select eno,ename,esal, rank() over(order by esal desc nulls last) as e_rank from emp order by esal desc nulls last) where e_rank =2;

Report message to a moderator