-2

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.

Mukesh Adhvaryu
  • 630
  • 5
  • 16
  • Read Ask Tom regarding Top-n and pagination: http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html and http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html – Kris Johnston May 11 '16 at 20:27
  • Are you storing either a created date type in the table or maybe a sequence? If not, no solution may be possible since Oracle doesn't store the order by which rows were added to a table (you can't use rowid, rownum, or any default ordering to get the order the rows were inserted to the table). – Kris Johnston May 11 '16 at 21:30
  • Possible duplicate of [Oracle SQL - How to Retrieve highest 5 values of a column](http://stackoverflow.com/questions/2306744/oracle-sql-how-to-retrieve-highest-5-values-of-a-column) – MT0 May 11 '16 at 23:39
  • @MT0 it is not duplicate. I don't want 5 highest values. I want 5 top values. You should have read question carefully before jumping on duplicate bandwagon. It is about ordinal position the physical order. – Mukesh Adhvaryu May 12 '16 at 00:52
  • @KrisJohnston thank you very much for the link. It's really helpful to understand. – Mukesh Adhvaryu May 12 '16 at 01:03
  • @MukeshAdhvaryu I read the question very carefully, you want the first n rows for a given order. It just happens that that order is insertion order rather than descending value order - the difference between the two is just the specification of a different `ORDER BY` clause. So, yes, it is a duplicate you can take the answers from that question and just apply a minimal change of a different `ORDER BY` clause. – MT0 May 12 '16 at 08:22
  • You can also see [other answers about limiting the number of rows](http://stackoverflow.com/q/470542/1509264) or about [getting rows in insertion order](http://stackoverflow.com/q/3266273/1509264). – MT0 May 12 '16 at 08:26
  • @MT0 again I say respectfully I will have to disagree with this. If you have read my answer carefully please explain when I said "I want data in order in which they are added" which means physical order. So yes you missed it where do I talk about Order By clause? Did I mention it? Can you get physical order after applying Order By clause? If so tell me how? I never mentioned any order except physical order. Please read it again. And tell me where I did mention Order By clause? – Mukesh Adhvaryu May 12 '16 at 13:16

1 Answers1

3

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;
MT0
  • 86,097
  • 7
  • 42
  • 90
  • Your answer does not address my question. I repeat I want top n rows in order they are added in table. Please provide me with answer addressing that. – Mukesh Adhvaryu May 12 '16 at 01:12
  • @MukeshAdhvaryu You cannot get the rows in the order that they are added to the table unless (a) you maintain a column with a unique column that you assign incrementing value to each successive insert (in which case you can order by that as I have shown) or (b) if you never delete and then insert rows from the table (or have row movement) **and** the database inserts the data into the datafiles in the order it was created and reads back in that same order (neither of which are guaranteed) then specifying no order by can work (as per my second query). – MT0 May 12 '16 at 08:11
  • +1 Nice, complete answer... I considered submitting a very similar answer as yours, but since it wasn't clear if there was a sequence or created date data type on the table, I decided to just leave a comment on the question and move on... – Kris Johnston May 12 '16 at 12:45
  • Well for what it's worth, I consider this answer accepted because there is not an answer at all. We can not get physical order of rows unless we do some magic trick. Pity isn't it? – Mukesh Adhvaryu May 12 '16 at 13:22
  • @MT0 although I accepted your answer, I still maintain that it was not a duplicate question and that is evident from your own answer when you say "You cannot get the rows in the order that they are added to the table unless..." Anyway it doesn't make difference does it? – Mukesh Adhvaryu May 12 '16 at 13:26