4

I am making a query and trying to fetch a random number of rows from a database but Oracle keeps telling me my command has not been ended properly. 

select *
from random_table
order by random_column_name
offset 0 rows
fetch first 10 rows only

This is the code I'm using.

I would greatly appreciate any support since I've spent quite some time searching for an explanation for the outcome I'm getting, but to no avail.

a_horse_with_no_name
  • 440,273
  • 77
  • 685
  • 758
Andrey.Kyurkchiev
  • 61
  • 1
  • 1
  • 9
  • 1
    This is an Oracle 12 feature yet your tag says Oracle 11g, see http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering – kevinskio Jun 15 '16 at 13:10
  • Thank you. I was not aware that feature was unavailable for my version. – Andrey.Kyurkchiev Jun 15 '16 at 13:12

5 Answers5

9

That syntax isn't valid until Oracle Database 12c.

You would say instead

select *
from random_table
where rownum < 11
order by random_column_name;
thatjeffsmith
  • 15,972
  • 2
  • 27
  • 87
  • Thank you. I was not aware that feature was unavailable for my version. – Andrey.Kyurkchiev Jun 15 '16 at 13:12
  • Here's the 12c New Features list https://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS113 – thatjeffsmith Jun 15 '16 at 13:14
  • 3
    Syntactically, that is valid. Functionally, however, it does a very different thing. This query returns an arbitrary 10 rows from the table not necessarily the 10 rows with the top `random_column_name` values. You'd have to have a nested subquery with the `order by` clause and the `rownum <= 10` clause outside the nested subquery. – Justin Cave Jun 15 '16 at 14:46
  • row order is never guaranteed w/o an ORDER BY clause, and yes you could use an analytical function to achieve the same as the FETCH syntax - if you look at execution plan in 12c, that's exactly what the db is doing implicitly – thatjeffsmith Jun 15 '16 at 15:37
  • i didn't put the order by in b/c i was lazy, oops – thatjeffsmith Jun 15 '16 at 15:37
  • Order by has to come after the Where clause in Oracle. In the order of execution, the "order by" will not occur until after the where clause has already been executed which means you cannot guarantee which rows will be returned anyways with this method. FROM > WHERE > GROUP BY > Having > SELECT > ORDER BY > OFFSET - FETCH (If it were available) – crumdev Oct 30 '19 at 16:40
  • This doesn't work if you are using GROUP BY and ORDER BY together and want to limit the result to 1 row after the ORDER BY operation. – Ani Aggarwal Nov 19 '19 at 22:21
3

MSSQL:

offset 10 rows fetch first 10 rows only

Oracle:

select *
from random_table
order by random_col
where rownum >= 10 and rownum < 20
Wei Bangtuo
  • 61
  • 1
  • 7
3

Oracle (works on the versions before 12c):

select *
from (
  select *, rownum rn
  from (
    select *
    from random_table
    order by random_col) t
  where rownum <= 20) f
where rn > 10
Elena Mironova
  • 31
  • 2
2

None of the above worked for me in Oracle 11.2, so here's another way

select * from (
  select random_table.*,
         row_number() over (ORDER BY update_date DESC) line_number
   FROM random_table where status = 100
) WHERE line_number between 5 AND 10

In the example above, I've added filtering on my status and ordering by my latest update_date, but could be anything.

Here the pagination is determined by start point (5) and end point (10), shown above. This makes it easy to calculate based upon page number and page size (page 2 with page size 5 in this case), if needed.

Brian
  • 410
  • 5
  • 11
1

I did it this way, so I can do an orderly search and I don't risk not seeing any results.

WITH lista AS(SELECT *
            FROM random_table
           ORDER BY random_column_name)
SELECT *
  FROM lista
 WHERE ROWNUM <= 10;
Bruno Júnior Góis
  • 11
  • 1