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

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.

Hope this helps.

/Jacob

--
Jacob Steen Due                              RAMBOLL Informatics and Management 

email: jcd_at_ramboll.dk

'The opinions expressed by me are my own, not my employers - I think'