Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: about finding the nth value using a QUERY - urgent
Try:
select a.sno, a.marks
from your_table a
where 1 = (select count(*) from your_table
where marks > a.marks);
It works well if marks is a unique column. Or you could end up with no rows or more than 2 rows retrieved.
If you change subquery to:
select count(distinct marks) from .....
It works even if marks is not unique. But it treats 101 as second largest even if there is another row with marks = 102 in your example.
Hth
bjin
vvraghav_at_my-dejanews.com wrote:
> hi ,
> i am just learning sql (using sql plus 3.3 with PO7win95) .
> i have an assignment i have to complete -
> to find the nth maximum value from a given list ....
> say i want to find the 2nd maximum from the following list
>
> sno (primary key) marks
> 1 100
> 2 102
> 3 101
> 4 97
> the output should be (3 101) and the catch is i shouldnt use
> pl/sql programs , only simple queries.
> (even if order by using marks, how do i print only the nth row?)
> vikram , india
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Mar 25 1999 - 21:32:07 CST