CODEWITHSUNDEEP
sqloracle-databasenative-sql
Kushal Chowdhury
Kushal Chowdhury

Reputation: 11

Select n number of records from oracle database

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?

Upvotes: 1

Views: 5210

Answers (3)

MT0
MT0

Reputation: 167962

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;

Upvotes: 5

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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.

Upvotes: 2

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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.

Upvotes: 0

Related Questions