5

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

Koshuta
  • 85
  • 1
  • 1
  • 3

1 Answers1

5

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.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thank you, I did it with select distinct and rownum <= 10 – Koshuta May 22 '15 at 10:50
  • @Koshuta Also, remember, distinct is applied on all the columns in the select list and not just column you put after distinct. – Lalit Kumar B May 22 '15 at 10:52
  • "The first 10 rows" is not the same as ["any 10 random rows."](http://stackoverflow.com/a/19153620/712526) Here's a PL SQL implementation: `select * from table where ROWNUM <= 10 ORDER by dbms_random.value;` – jpaugh Oct 26 '16 at 20:44