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

or use the "where rownum < 5" in your sql statement

regards
Abdul

jploeg_at_pi.net (Jack Ploeg) wrote:

>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 integer;
>begin
> 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;
> end loop;
>end;
 

>>Thanks
 

>>Ramesh
>Bye,
 

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