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

"Muhammad A. Malik" <mamalik_at_cs.wmich.edu> wrote:
>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???
>>
>> Thanks
>>
>> Ramesh
>
>
>SELECT x_date
>FROM y_table a
>WHERE 4 > (SELECT count(*)
> FROM y_table
> WHERE x_date < a.x_date)
>ORDER BY x_date desc
>
>
>Some readjusment in '<' or '>' sign in the above query will definitly
>solve your problem.
>--
>
>Muhammad A. Malik
>mamalik_at_cs.wmich.edu
>+1 (616) 342-4569

I think the solution suggested by Muhammad would have some problems if more than one row having the same x_date value. For example if the minimum x_date occurs more than 4 times, then you will never ever get this x_date because the count(*) is greater than 4.

Personally, I couldn't think of a "PERFECT" SQL solution to this problem. The previous one suggested by jcd involving using index will put much burdern to users and the database administrator.
So if anybody can formulate this in a single simple SQL statements. Please tell me.

Cheers,
Wilfred.
UCL, London. Received on Wed May 22 1996 - 00:00:00 CEST