Select n number of records from oracle database

Question

I have to select n number of rows from an oracle database from sap using native sql. In open sql the query would be like

    select * from myDB where size > 2000 upto n rows.

what I learnt from other posts the equivalent native sql query would be

    SELECT * FROM myDB WHERE SIZE > 2000 AND rownum <= 100

is this correct?

Do I need to have rownum as one of the fields in the DB table?

Yea but you already have the oracle equivalent **rownum** why doesnt that work — code, Aug 19 '15 at 16:20
without an order by the result set returned would be random. Do you want a random selection of records > 2000 or is an order needed? Maybe you need something like this? http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm — xQbert, Aug 19 '15 at 16:23
"Do I need to have rownum as one of the fields in the DB table?" No, ROWNUM is built into Oracle. — Shannon Severance, Aug 19 '15 at 18:21
If you are on Oracle 12, you can use `SELECT * FROM myDB WHERE SIZE > 2000 fetch first 100 rows only` — , Aug 20 '15 at 06:53

score 4 · Answer 1 · answered Aug 20 '15 at 10:57

SELECT *
FROM   myDB
WHERE  SIZE   > 2000
AND    ROWNUM <= 100

Is a syntactically correct query.

Do I need to have rownum as one of the fields in the DB table?

No, the ROWNUM pseudocolumn assigns, for each row returned by a query, a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Note that ROWNUM is applied before any ORDER BY clauses.

So:

SELECT *
FROM   myDB
WHERE  ROWNUM <= 10
ORDER BY column_name

Will select 10 rows (it could be any 10 rows and not necessarily the 10 rows which are first in the desired ordering) and then will order those 10 rows by the desired column.

If you want to get the rows with the top-N values for a column then you need to apply the ORDER BY first and then restrict the number of rows:

SELECT *
FROM   ( SELECT *
         FROM   myDB 
         ORDER BY column_name )
WHERE  ROWNUM <= 10

If you are using Oracle 12 then they have implemented a new syntax for TOP-N queries:

SELECT *
FROM   myDB
ORDER BY column_name
FETCH FIRST 10 ROWS ONLY;

Bob Jarvis - Слава Україні · Answer 2 · 2015-08-20T10:38:24.280

ROWNUM is a pseudocolumn generated by Oracle whenever you perform a SELECT. The assignment of the value to ROWNUM is the last thing done before the query returns a row - thus, the first row emitted by the query is given ROWNUM = 1, the second row emitted by the query is given ROWNUM = 2, and so on. Notice: this means that a statement like the following WILL RETURN NO ROWS:

SELECT *
  FROM SOME_TABLE
  WHERE ROWNUM >= 2

Why does this return no rows? It's because the first row which would be emitted by the query is always given ROWNUM = 1, and since the query is looking only for rows with ROWNUM >= 2 no rows will be selected because the first ROWNUM value of 1 is applied to the first row emitted.

HOWEVER - if you really want to get all rows EXCEPT the first (or the first 10, or what have you) you can do it like this:

SELECT *
  FROM (SELECT *, ROWNUM AS INNER_ROWNUM
          FROM SOME_TABLE
          WHERE SOMETHING = SOMETHING_ELSE
          ORDER BY WHATEVER) i
  WHERE i.INNER_ROWNUM IN (1, 2, 3, 5, 7, 11, 13, 17, 19);

Here we use an inner SELECT which gets ALL rows for the given condition (SOMETHING = SOMETHING_ELSE) and assigns the ROWNUM from the INNER query to a computed column named INNER_ROWNUM, which we can then use as a normal column in the outer query.

Good answer but you're missing an `ORDER BY` in that inner query - otherwise it makes no sense to get row "5" but not row "4". — Jeffrey Kemp, Aug 20 '15 at 08:09
"ROWNUM is a pseudocolumn generated by Oracle whenever you perform a SELECT" ... or an update or a delete as well, of course. And in fact rownum is applied before any aggregation step happens as well, hence: "select count(*) from ... where rownum < 2" can only return 0 or 1, and is a god test for row existence. — David Aldridge, Aug 20 '15 at 11:03

score 0 · Answer 3 · answered Aug 20 '15 at 05:00

Use rownum concept it is a pseudo column in oracle by using this concept we can achieve the TOP keyword functionality.

select col1,col2,col3..coln from
(
select rownum rn,e.* from mydb where size>2000
)
where rn>N

Note: Col1 to coln are column names of your table.

Select n number of records from oracle database

3 Answers3