CODEWITHSUNDEEP
sqloracle-databaseselect
Koshuta
Koshuta

Reputation: 85

How to select 10 rows from column in Oracle

I have table in Oracle with column "identification number" which contains identification number for customers. I want to select 10 optionly identity numbers - how to do that; can you help me?

P.S. I'm a newbee to Pl/SQL

Upvotes: 5

Views: 32391

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

If you just want any 10 random rows, then just use ROWNUM:

SELECT * FROM table_name 
   WHERE ROWNUM <=10;

If you want 10 rows for a specific set of values that meet the condition, then add a filter predicate:

SELECT * FROM table_name 
WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10);

If there are duplicate values for identification number column, then again add ROWNUM to filter the rows, the rows would again be randomly selected:

SELECT * FROM table_name 
   WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10) 
   AND   ROWNUM <=10;

If you want 10 rows in a particular ORDER, then you must first sort them and then apply ROWNUM to limit the number of rows in the final output:

SELECT * FROM(
   SELECT * FROM table_name 
      WHERE identification number IN (1, 2, 3,4, 5, 6, 7, 8, 9, 10) 
    ORDER BY identification number
             )
  WHERE ROWNUM <=10;

You might also be interested in the new Top-n row limiting feature introduced in Oracle 12c.

Upvotes: 5

Related Questions