Nth maximum value 1

[spradeepraj]

How to find out the Nth maximum value of a Numeric Column. mail me : spradeepraj@dsqsoft.com
Delphidhasan

 

[Turkbear]

Hi,
AFAIK you can't directly...
If you mean how to get the
10th highest value ( for instance) you would need to use PL/Sql code and loop thru the values to determine that.

 

[Custom24]

I put it in here so that other people could see it.

select NumericColumn from(
select rownum Ordinal, NumericColumn from Table order by NumericColumn desc)
where ordinal = <Your Number>;

Hope this helps
Mark

 

[Custom24]

PS - the Numeric Column has to be indexed for this to work (I think)
Mark

 

[Turkbear]

Hi,
I tried your solution on some data I have and it produces an incorrect result--Did I miss something?

  1  select cmptn_rt from (select rownum Ordinal,cmptn_rt from hr_cur_rptng
  2   order by cmptn_rt desc)
  3* where ordinal = 10
SQL> /

  CMPTN_RT
----------
     19.02

SQL>

That is nowhere near the 10th highest cmptn_rt.
( cmptn_rt is an indexed field)

 

[karluk]

I think the problem with Custom24's suggestion is that the rownum in the subselect is determined before the sort. That means selecting the tenth &quot;ordinal&quot; doesn't correspond to the tenth highest value.

If are willing to select the top ten values, you can do this with one subselect. Otherwise you will probably need two.

To get the top 10:

select NumericColumn from(
select NumericColumn from Table order by NumericColumn desc)
where rownum <= 10;

To get the tenth highest:

select NumericColumn from (
(select NumericColumn from(
select NumericColumn from Table order by NumericColumn desc)
where rownum <= 10;
order by NumericColumn)
where rownum = 1;