Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: about finding the nth value using a QUERY - urgent

Re: about finding the nth value using a QUERY - urgent

From: bjin <jinbo_at_hpsgns1.sgp.hp.com>
Date: Fri, 26 Mar 1999 11:32:07 +0800
Message-ID: <36FAFFB7.599F7BD4@hpsgns1.sgp.hp.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US