What is the purpose of using ROWNUM in SQL?

1 year ago
Benjamin Taylor
1 minute

In SQL, ROWNUM is a pseudo column used to limit the number of rows returned in the result set. Its usage is as follows:

  1. Choose
  2. The sequence number of a row in a database table
SELECT * FROM table_name WHERE ROWNUM <= 10;

The above query will retrieve the first 10 rows from the table called table_name.

  1. Sort by
  2. ROWNUM refers to a pseudocolumn in Oracle that numbers each row in the result set of a query.
SELECT * FROM (
  SELECT t.*, ROWNUM AS rnum FROM (
    SELECT * FROM table_name ORDER BY column_name
  ) t
) WHERE rnum >= 11 AND rnum <= 20;

The above query will return rows 11 to 20 in the table_name table sorted by column_name.

It is important to note that ROWNUM is calculated after the query results are returned. Therefore, when using ROWNUM for pagination, the query results need to be processed as a subquery in order to correctly obtain pagination results.

bannerAds