Re: Sql Query

From: Mark Sheng <mark_sheng_at_forcorp.com>
Date: Wed, 22 Nov 2000 16:58:45 GMT
Message-ID: <3A1BFBC9.B1865D1@forcorp.com>

also how about this one:

select * from (select total,rownum row_num from (select distinct total from emp order by total desc)) where row_num=4;

Mark

David Fitzjarrell wrote:

> In our last gripping episode sigdock_at_my-deja.com wrote:
> > Try the following query:
> >
> > select id
> > , total
> > from orders
> > where rownum < 5
> > order by total desc
> > /
> >
> > Should work...
> >
> > h.t.h.
> >
> > BertJan Meinders
> > Oracle DBA
> > ASR-ICT
> >
> > In article <8vgltk$mq3$1_at_nnrp1.deja.com>,
> > Paul <elrik13269_at_my-deja.com> wrote:
> > > Hi,
> > > I,m new to sql and wonder if anybody could help me with the
 following:
> > > If I had a table with say order id and order total i.e
> > >
> > > id total
> > > 213 567.35
> > > 345 444.00
> > > 214 32.99
> > > and so on how would I find say just the fourth highest total from
 the
> > > table without displaying the rest of the table.
> > > Thanks for any help.
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >

>

> A valiant effort, but not quite correct. Since the ROWNUM is assigned
> when the data is retrieved, not when it is sorted, one will return the
> firswt four rows from the table; that subset will then be ordered, by
> total, in descending order. This subset may or may not include the
> fourth highest total in the table, and will most certainly include more
> entries than just the one requested.

>

> As stated the request is to return the fourth highest total from the
> table -- one row, one set of values that meets the specified criteria.
> If ROWNUM was assigned AFTER the data is ordered the above query would
> then be the first part of the solution. However I cannot see a simple
> SQL*Plus solution to the request. I propose the following:

>


> declare

>      cursor get_ordered_data is

>      select id, total

>      from orders

>      order by total desc;

>      ctr     number:=0;

> begin

>      for order_rec in get_ordered_data loop

>          ctr := ctr + 1;

>          if ctr = 4 then

>               dbms_output.put_line('Order ID: '||order_rec.id||'

> Total: '||order_rec.total);

>          end if;

>      end loop;

> end;
> /

>

> Or another PL/SQL solution -- this solution presumes that there will be
> more than four records in the ORDERS table although it should work with
> fewer rows (I have tried this with three rows in the table and it
> returns the lowest total and exits successfully):

>


> declare

>      cursor get_orders is

>      select id, total

>      from orders

>      order by total desc;

>      ordid     orders.id%type;

>      ordttl    orders.total%type;

>      ctr       number:=0;

> begin

>      open get_orders;

>      loop

>           fetch get_orders into ordid, ordttl;

>           ctr := ctr + 1;

>           exit when ctr > 4;    -- Total has been printed, exit

>           if ctr = 4 then

>                dbms_output.put_line('Order ID: '||ordid||'

> Total: '||ordttl);

>           end if;

>      end loop;

>      close get_orders;

> end;
> /

>

> I can imagine that someone will have the requisite time to devote to
> this matter to divine a pure SQL*Plus solution, however I cannot, at
> the current moment, see that one is possible.

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Nov 22 2000 - 10:58:45 CST