Oracle ROWNUM: Basic Usage Guide

ROWNUM is a pseudocolumn that can be used to return the row numbers in a result set. In Oracle, ROWNUM is assigned after the data is retrieved, therefore it cannot be directly used in the WHERE clause. Here is the basic usage of ROWNUM:

1. Retrieve the first N rows of data:
   SELECT * FROM table_name WHERE ROWNUM <= N;
2. Pagination query:
   Select all columns from the table “table_name” ordered by “column_name”, limiting the result set to a specific number of rows equal to the page size starting from a particular row number.
3. This query first sorts the result set, and then uses ROWNUM for paging. “start” is the index of the starting row, and “page_size” is the number of rows displayed per page.
4. Delete duplicate data using ROWNUM and exclude the WHERE clause:
   DELETE FROM table_name WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column_name);
5. This query uses ROWNUM to remove duplicate data in a table. The inner SELECT statement first finds the minimum rowid for each duplicate data group using the GROUP BY clause and MIN function. Then, the outer DELETE statement uses ROWNUM to exclude these minimum rowids, effectively deleting the duplicate data.