Re: How to get the first four rows???
Date: 1996/05/20
Message-ID: <4npjbt$kag_at_berta.dde.dk>#1/1
jcd_at_rhh.dk (jcd) writes:
>Ramesh Garapaty (gramesh_at_ix.netcom.com) 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???
>Assuming that you do this often, and that you does not have any performance
>problems while inserting/updating/deleting in the 'y_table' you could
>create an index on the 'x_date' (and possibly the 'desc' field too) and then
>'trick' Oracle into using the index by adding somting along the lines of
>'where x_date >= to_char('19900101','YYYYMMDD') and rownum < 5' to the select.
>By creating the index in ascending or descending order and by changing the
>where-clause you can switch between returning in either order.
>Performancewise (select) this will be very fast, since Oracle only looks up
>the rows you actually want - in large tables this will make a very dramatic
>difference.
>The downside is that you will use more diskspace, and that inserts, deletes
>and updates will be slower.
>/Jacob
>--
>Jacob Steen Due RAMBOLL Informatics and Management
>'The opinions expressed by me are my own, not my employers - I think'
Another approch is to have oracle do the sorting 'before' the counting. As you have seen using order by oracle will do the sorting 'after' the numbering of records.
Use an operator like 'union' to do sorting before counting - that is a union with noting - not to spoil the result:
SELECT x_date
FROM y_table
where rownum < 5
union
select 1, 2, 3 ...
from dual
where 1 = 2;
Only drawbacks are performance if we are talking masive amounts of rows and that the sequence of x_date has to follow the implicit order by sequence.
My 5 cents. Received on Mon May 20 1996 - 00:00:00 CEST