When did oracle start supporting "top": select top ? p2_.PRODUCT_ID from PRODUCT?

Question

When did oracle start supporting "top":

select top ? p2_.PRODUCT_ID from PRODUCT?

Answer 1

Oracle does not support the TOP keyword. The query

SELECT TOP 10 product_id
  FROM product

will generate a syntax error because the TOP 10 clause is not recognized, at least through Oracle 11.1 (the current production release).

Answer 2

I am not sure ORACLE ever had a TOP function. You want to use a TOP-N query.

For example:

select  *
  from  (SELECT  *
           FROM  foo
          where  foo_id=[number]
       order by  foo_id desc)
 where  rownum <= 3

This will get you the top three results (because I order by desc in the sub query)

Answer 3

TOP is supported by SQL server not by Oracle. Don't confuse with FIRST_ROWS hit that is for retrieve the best plan for the firs rows not to stop after a number of results.

As other said, the Oracle solution is to play with ROWNUM pseudocolumn.