Re: Sql Query

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;

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;

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.