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?

Answer 1

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.

Answer 2

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.

Answer 3

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;