In Oracle, you can limit the number of rows returned by a query by using the "ROWNUM" keyword in your query's "WHERE" clause. The "ROWNUM" keyword assigns a unique number to each row in the result set, starting at 1. You can limit the number of rows returned by the query by including a condition in the "WHERE" clause that compares the "ROWNUM" to a specific value.
For example, if you want to limit the number of rows returned to 5, you can use the following query:
ORDER BY mycolumn
WHERE ROWNUM <= 5;
Alternatively, you can use the "FETCH FIRST" clause
ORDER BY mycolumn
FETCH FIRST 5 ROWS ONLY;
It's important to note that the order of the rows returned is not guaranteed when using ROWNUM, So if you want to order the rows before limiting the number of rows returned, you should use "ORDER BY" clause before "WHERE ROWNUM <= n" or "FETCH FIRST n ROWS ONLY."
Also, the ROWNUM is a pseudo column, it's not a physical column in the table, so it's not an efficient way to retrieve a limited number of rows, especially when working with large datasets. A better approach is to use the "OFFSET" and "FETCH" clauses that were introduced in Oracle 12c.
ORDER BY mycolumn
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
This will retrieve the first five rows after ordering the rows by mycolumn
0 Komentar