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

From: Victor <slootsky_at_tst.tracor.com>
Date: Mon, 29 Mar 1999 15:27:45 -0500
Message-ID: <36FFE241.D1D92567@tst.tracor.com>

Sorry, Arnold, but rownum=2 will never work.

Here is the solution:

TASK:
Write a script for an interactive process. Please, return a specific (Nth) maximum value of the field SAL from EMP table.
(N might be 1, 2, 3, 4,...)
Write a script which will accept input for value of nmax. Then according to the value input, the script must return the Nth maximum value from the table.

SLUTION:
SQL> l
  1 select ename, sal from emp a
  2 where &nmax = (select count(distinct sal)

  3                              from emp b
  4*                             where a.sal <= b.sal)

SQL> /
Enter value for nmax: 4
old 2: where &nmax = (select count(distinct sal) new 2: where 4 = (select count(distinct sal)

ENAME SAL
---------- ---------
BLAKE 2850 My best to Vikram.

VIctor Slootsky
Oracle DBA

Arnold Schommer wrote:

> 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
>
> try rownum. (This is a pseudo-column). assuming the above table is
> called "rankings", it should be:
>
> select sno, marks
> from rankings
> where rownum = 2
> order by marks desc;
>
> maybe this helps you.
>
> Arnold Schommer
Received on Mon Mar 29 1999 - 14:27:45 CST