Re: How to get the first four rows???

gramesh_at_ix.netcom.com(Ramesh Garapaty) wrote:

>Hi!
>I have a SQL as follows:
>SELECT x_date
>FROM y_table
>ORDER BY x_date desc
 

>However I want the first four rows only. If I say WHERE rownum < 5 it
>returns the rows even before sorting by descending order. All I want to
>see is the latest four dates from the database. Is there a SQL with
>which we can do this???

Right, rownum is assigned on retrieval of the rows, so that's always before sorting. Using SQL you could:
a. select the row with the maximum date (using max(), this would return the first row)
b. then use max() again, but limiting the rows to the ones with x_date larger than the date you found in step a. c. repeat this two more times
d. select all four using UNION

This would only work if all x_dates were different.

It's easier using a PL/SQL cursor:

declare
  cursor c_date is

     select x_date from y_table
     order by x_date desc;

   x:=1;
   for n_date in c_date loop

      dbms_output.put_line(n_date.x_date);
      x:=x+1;
      if x>4 then
         exit;
      end if;

>Thanks
 

>Ramesh

Bye,

Jack. Received on Fri May 17 1996 - 00:00:00 CEST