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.
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:
WHEREclause filters are applied (and the
ROWNUMpseudo-column is generated for each row that matches all the
ORDER BYclause 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;