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

From: schong <schong_at_werple.mira.net.au>
Date: Thu, 25 Mar 1999 03:49:42 -0800
Message-ID: <36FA22D6.4631@werple.mira.net.au>

Try this :-

  select *
  from
  (
    select rownum rank, a.*
    from
    (

     select sno, marks
     from   rankings
     order  by marks desc

    ) a
  )
 where rank = <the nth maximum value>;

 Now do you homework!!

 Clement

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 Thu Mar 25 1999 - 05:49:42 CST