You can use the "ROWNUM" keyword in Oracle to retrieve the top 1 row from a query. The "ROWNUM" keyword assigns a unique number to each row in a result set, starting from 1. You can use this keyword in a WHERE clause to retrieve only the first row.
The basic syntax is as follows:
SELECT column1, column2, ...
FROM table
WHERE ROWNUM <= 1;
For example, the following query retrieves the first employee in the "employees" table:
SELECT first_name, last_name
FROM employees
WHERE ROWNUM <= 1;
Alternatively, you can use the "FETCH FIRST" clause in combination with the "ORDER BY" clause to retrieve the top 1 row from a query. The basic syntax is as follows:
SELECT column1, column2, ...
FROM table
ORDER BY column1, column2, ...
FETCH FIRST 1 ROWS ONLY;
For example, the following query retrieves the first employee in the "employees" table based on their last_name.
SELECT first_name, last_name
FROM employees
ORDER BY last_name
FETCH FIRST 1 ROWS ONLY;
It's worth noting that the "FETCH FIRST" clause is available in Oracle Database 12c or later, and the ROWNUM keyword is general in all versions of Oracle.
It's also worth noting that the ROWNUM keyword is not guaranteed to return the top 1 in the order you expect. It assigns a unique number to each row before any sorting, so depending on the order of the rows in the table, the ROWNUM keyword might not return the top 1 row based on the sorting criteria you have used in the query.
0 Komentar