Mukesh Adhvaryu Mukesh Adhvaryu - 6 months ago 34
SQL Question

what is the select top equivalent in oracle10g

I am currently working on an application which queries database- Oracle 10g.
Is there any thing equivalent in Oracle to get the same as we use Top(x) in SQL Server? I want Data in order it is added in table.
Any help would be appreciated.

Please note that I don't want 5 highest values. I want 5 top values. It is about ordinal position the physical order in which rows added to the table. No any sorting even by index.

MT0 MT0
Answer

Assuming that you are inserting data with an incrementing value for the primary key (via a sequence or a date/timestamp value) then you can do:

SELECT *
FROM   (
  SELECT *
  FROM   table_name
  ORDER BY primary_key_column
)
WHERE ROWNUM <= 10;

You need the outer query as the order in which execution happens in a query is:

  1. The WHERE clause filters are applied (and the ROWNUM pseudo-column is generated for each row that matches all the WHERE clause filters);
  2. Then the ORDER BY clause is applied.

Applying this in a single query will get the first 10 rows found in the database and will then order those rows by the primary key (not what you want). Using an inner query you can force the ORDER BY clause to be applied first and then the filtering on the required number of rows will occur subsequently in the execution of the outer query.

If you don't have an incrementing primary key then you will have to rely on just ROWNUM without any ORDER BY clause - however, if the table has row movement enabled or if you delete a row and then insert a different row (in which case the database may fill the empty space with the new row) then you may find rows are not retrieved in insertion order. If you can live with this then just do:

SELECT *
FROM   table_name
WHERE  ROWNUM <= 10;
Comments