Oracle SQL is a robust language that provides users far-reaching control over databases. This includes the ability to precisely dictate how many results a query returns through the use of the LIMIT command. This aspect is particularly useful when you are dealing with large databases and only need a specific number of results from a query.
The LIMIT command is an optional part of the SELECT statement. It allows you to select a limited number of rows from a table. When used with the OFFSET value, the LIMIT command provides an excellent way of implementing paging. Many relational databases support the LIMIT clause such as MySQL, HSQL, and PostgreSQL. However Oracle SQL does not; but there’s a workaround using rownum or the newrow fetch clause.
How to limit results in Oracle SQL
The most common way of limiting results in Oracle SQL is by using the ROWNUM pseudo column or row fetch clause, since the traditional LIMIT command does not exist in Oracle.
SELECT column
FROM (
SELECT column, ROWNUM row_number
FROM table
WHERE row_number <= X
);
[/code]
Explaining the Code
In the code above, the SELECT command is used initially to define the column to be queried. This is followed by a nested selection, within which you again specify the column, this time alongside the ROWNUM pseudo column which Oracle automatically assigns as rows are selected, numbering from 1.
A WHERE clause is then used to dictate that only rows with a ROWNUM value less than or equal to a specified value ‘X’ are to be returned. This essentially limits the number of rows outputted by the query to the top X number.
Alternative Method – FETCH FIRST Clause
Starting with Oracle 12c, Oracle introduced the FETCH FIRST clause which can substitute for the ‘LIMIT’ clause from other SQL dialects.
[code lang=”Oracle SQL”]
SELECT column
FROM table
FETCH FIRST X ROWS ONLY;
In this snippet, the FETCH FIRST command is used after specifying the column and table, followed by the number of rows you wish to return, with the term ‘ROWS ONLY’ following, indicating that you want the system to fetch only the top X rows.
Using both, the ROWNUM pseudo column or the new FETCH FIRST clause, Oracle SQL developers have control over precisely how many lines a query returns, offering flexibility when dealing with substantial datasets.
Remember: Oracle doesn’t support the LIMIT command as found in some SQL dialects. Instead, you will have to use the ROWNUM pseudo column or the FETCH FIRST clause to limit your results.
