You can use the "ROWNUM" keyword in Oracle to select the top "n" records from a query result. The basic syntax of the query is as follows:
SELECT column1, column2, ...
FROM table_name
WHERE ROWNUM <= n;
Where:
- "column1, column2, ..." is the list of columns you want to select.
- "table_name" is the name of the table you want to select data from.
- "n" is the number of records you want to select.
For example, to select the top 10 records from the "employees" table, you can use the following query:
SELECT last_name, first_name, salary
FROM employees
WHERE ROWNUM <= 10;
This query will return the last name, first name, and salary of the first ten employees in the "employees" table.
Alternative:
SELECT column1, column2, ...
FROM (SELECT column1, column2, ...
FROM table_name
ORDER BY column1)
WHERE ROWNUM <= n;
This query will return the first ten records sorted by column1, it is useful when you want to select top records based on some specific criteria.
Please note that the ROWNUM keyword is applied before the result set is returned, so it can be less efficient than using a subquery or the FETCH clause with the ORDER BY clause.
Another alternative to this is using the keyword "FETCH FIRST" available in Oracle 12c and later versions. It is more efficient than using ROWNUM as it is applied after the result set is returned. The syntax is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1
FETCH FIRST 10 ROWS ONLY;
This query will return the first ten records sorted by column1, which is more efficient than ROWNUM.
0 Komentar