Koshuta - 1 month ago 7

SQL Question

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

Answer

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.

Source (Stackoverflow)

Comments