Oracle SQL ORDER BY Clause Tutorial: Row Limiting & Pagination Examples

Oracle SQL | ORDER BY Clause and Row Limiting

Understanding ORDER BY and ROWNUM
Basic Row Limiting - First N Rows
1. First N rows in ascending order
2. First N rows in descending order
Basic Row Limiting - Last N Rows
1. Last N rows in ascending order
2. Last N rows in descending order
Pagination - Previous Page Navigation
1. Previous page in ascending order
2. Previous page in descending order
Pagination - Next Page Navigation
1. Next page in ascending order
2. Next page in descending order
Pagination - Specific Page Selection
1. Specific page in ascending order
2. Specific page in descending order

Understanding ORDER BY and ROWNUM

The ORDER BY clause sorts the result set based on specified column(s) in ascending (ASC) or descending (DESC) order. When combined with ROWNUM, you can implement row limiting and pagination functionality in Oracle SQL.

ROWNUM is assigned to rows before the ORDER BY clause is applied. This means you cannot simply use "WHERE ROWNUM <= 2 ORDER BY column" to get the top 2 sorted rows. Instead, you must use a subquery to first order the data, then apply the ROWNUM filter.

This table and sample data is used in the examples below:

CREATE TABLE table_order_by
(
    id1 NUMBER(2,0)
);

INSERT INTO table_order_by (id1) VALUES (1);
INSERT INTO table_order_by (id1) VALUES (6);
INSERT INTO table_order_by (id1) VALUES (7);
INSERT INTO table_order_by (id1) VALUES (4);
INSERT INTO table_order_by (id1) VALUES (5);
INSERT INTO table_order_by (id1) VALUES (2);
INSERT INTO table_order_by (id1) VALUES (3);

COMMIT;

Data Visualization:

Ascending order display		Descending order display
SELECT id1 FROM table_order_by ORDER BY id1 ASC;		SELECT id1 FROM table_order_by ORDER BY id1 DESC;
1	First 2 rows	7	First 2 rows
2	First 2 rows	6	First 2 rows
3		5
4		4
5		3
6	Last 2 rows	2	Last 2 rows
7	Last 2 rows	1	Last 2 rows

Basic Row Limiting - First N Rows
To select the first N rows from an ordered result set, you must use a subquery pattern. The inner query performs the ordering, and the outer query applies the ROWNUM filter. This ensures that the sorting happens before row numbering.
1. First N rows in ascending order
  This pattern retrieves the smallest N values from the dataset. The inner query sorts in ascending order, ROWNUM selects the first N rows, and the outer ORDER BY maintains the ascending display order.
```
-- (1,2) - (3,4,5,6,7)
SELECT  id1
FROM
(
    SELECT  id1
    FROM    table_order_by
    ORDER BY id1 ASC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display
ORDER BY id1 ASC
```
  Query result:
```
1
2
```
2. First N rows in descending order
  This pattern retrieves the largest N values from the dataset. The inner query sorts in descending order to bring the largest values to the top, ROWNUM selects the first N rows, and the outer ORDER BY maintains the descending display order.
```
-- (7,6) - (5,4,3,2,1)
SELECT  id1
FROM
(
    SELECT  id1
    FROM    table_order_by
    ORDER BY id1 DESC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display
ORDER BY id1 DESC
```
  Query result:
```
7
6
```
Basic Row Limiting - Last N Rows
To select the last N rows requires reversing the sort order in the subquery. This technique brings the "last" rows to the top of the intermediate result set, allowing ROWNUM to select them.
1. Last N rows in ascending order
  To get the last N rows in ascending order, the inner query sorts in descending order (bringing the largest values first), ROWNUM selects those N rows, then the outer ORDER BY sorts them back to ascending order for display.
```
-- (1,2,3,4,5) - (6,7)
SELECT  id1
FROM
(
    SELECT  id1
    FROM    table_order_by
    ORDER BY id1 DESC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display
ORDER BY id1 ASC
```
  Query result:
```
6
7
```
2. Last N rows in descending order
  To get the last N rows in descending order, the inner query sorts in ascending order (bringing the smallest values first), ROWNUM selects those N rows, then the outer ORDER BY sorts them to descending order for display.
```
-- (7,6,5,4,3) - (2,1)
SELECT  id1
FROM
(
    SELECT  id1
    FROM    table_order_by
    ORDER BY id1 ASC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display
ORDER BY id1 DESC
```
  Query result:
```
2
1
```
Pagination - Previous Page Navigation
Note: The following pagination queries work only when the columns used in the ORDER BY clause contain unique values. For non-unique columns, additional sorting criteria should be added to ensure consistent results.

These queries implement "previous page" functionality by finding rows that come before the current page's first row. This technique is useful for cursor-based pagination where you know the boundary values of your current page.
1. Previous page in ascending order
  To navigate to the previous page in ascending order, select rows with values less than the current page's first row value. The inner query uses descending order to get the immediately preceding rows, then the outer ORDER BY restores ascending order.
```
-- (1,2) - (3,4) - (5,6) - (7)
-- current page (3,4)
SELECT  id1
FROM
(
        SELECT  id1
        FROM    table_order_by
        WHERE   id1 < 3 -- [1,3,5,7]: First row of the current page
        ORDER BY id1 DESC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display per page
ORDER BY id1 ASC
```
  Query result:
```
1
2
```
2. Previous page in descending order
  To navigate to the previous page in descending order, select rows with values greater than the current page's first row value. The inner query uses ascending order to get the immediately preceding rows, then the outer ORDER BY restores descending order.
```
-- (7,6) - (5,4) - (3,2) - (1)
-- current page (5,4)
SELECT  id1
FROM
(
        SELECT  id1
        FROM    table_order_by
        WHERE   id1 > 5 -- [7,5,3,1]: First row of the current page
        ORDER BY id1 ASC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display per page
ORDER BY id1 DESC
```
  Query result:
```
7
6
```
Pagination - Next Page Navigation
Note: The following pagination queries work only when the columns used in the ORDER BY clause contain unique values. For non-unique columns, additional sorting criteria should be added to ensure consistent results.

These queries implement "next page" functionality by finding rows that come after the current page's last row. This approach provides efficient forward navigation through large result sets.
1. Next page in ascending order
  To navigate to the next page in ascending order, select rows with values greater than the current page's last row value. Both the inner and outer queries use ascending order to maintain the natural progression.
```
-- (1,2) - (3,4) - (5,6) - (7)
-- current page (3,4)
SELECT  id1
FROM
(
        SELECT  id1
        FROM    table_order_by
        WHERE   id1 > 4 -- [2,4,6,7]: Last row of the current page
        ORDER BY id1 ASC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display per page
ORDER BY id1 ASC
```
  Query result:
```
5
6
```
2. Next page in descending order
  To navigate to the next page in descending order, select rows with values less than the current page's last row value. Both the inner and outer queries use descending order to maintain the reverse progression.
```
-- (7,6) - (5,4) - (3,2) - (1)
-- current page (5,4)
SELECT  id1
FROM
(
        SELECT  id1
        FROM    table_order_by
        WHERE   id1 < 4 -- [6,4,2,1]: Last row of the current page
        ORDER BY id1 DESC
) t
WHERE   ROWNUM <= 2 -- Number of rows to display per page
ORDER BY id1 DESC
```
  Query result:
```
3
2
```

Pagination - Specific Page Selection

Note: The following queries demonstrate offset-based pagination, which is useful when you need to jump to arbitrary page numbers rather than navigate sequentially.

Note: The following pagination queries work only when the columns used in the ORDER BY clause contain unique values. For non-unique columns, additional sorting criteria should be added to ensure consistent results.

These queries implement offset-based pagination, allowing direct navigation to any page number. This approach calculates the exact row range needed for a specific page and is essential for implementing page number navigation in user interfaces.

Specific page in ascending order

This technique uses ROWNUM in a nested subquery approach. The innermost query orders the data, the middle query applies an upper bound with ROWNUM, and the outer query applies the lower bound.

-- (1,2) - (3,4) - (5,6) - (7)
-- page (3,4)
SELECT  id1
FROM
(
        SELECT
                id1
        ,       ROWNUM AS rnum
        FROM
        (
                SELECT  id1
                FROM    table_order_by
                ORDER BY id1 ASC
        ) t1
        WHERE   ROWNUM <= 4 -- [2,4,6,8]: (Rows per page) * (page number)
)
WHERE   rnum >= 3 -- [1,3,5,7]: (Rows per page) * ((page number) - 1) + 1
ORDER BY id1 ASC

Or using ROW_NUMBER function (provides a more modern alternative):

-- (1,2) - (3,4) - (5,6) - (7)
-- page (3,4)
SELECT  id1
FROM
(
        SELECT
                t1.id1
        ,       ROW_NUMBER() OVER (ORDER BY id1 ASC) AS rnum
        FROM    table_order_by t1
)
WHERE   rnum >= 3 -- [1,3,5,7]: (Rows per page) * ((page number) - 1) + 1
AND     rnum <= 4 -- [2,4,6,8]: (Rows per page) * (page number)

Query result:

3
4

Specific page in descending order

The same pagination logic applies for descending order, but the ORDER BY clauses use DESC. The row number calculations remain the same, as they are based on logical page positions rather than sort direction.

-- (7,6) - (5,4) - (3,2) - (1)
-- page (5,4)
SELECT  id1
FROM
(
        SELECT
                id1
        ,       ROWNUM AS rnum
        FROM
        (
                SELECT  id1
                FROM    table_order_by
                ORDER BY id1 DESC
        ) t1
    WHERE   ROWNUM <= 4 -- [2,4,6,8]: (Rows per page) * (page number)
)
WHERE   rnum >= 3 -- [1,3,5,7]: (Rows per page) * ((page number) - 1) + 1
ORDER BY id1 DESC

Or using ROW_NUMBER() function:

-- (7,6) - (5,4) - (3,2) - (1)
-- page (5,4)
SELECT  id1
FROM
(
        SELECT
                t1.id1
        ,       ROW_NUMBER() OVER (ORDER BY id1 DESC) AS rnum
        FROM    table_order_by t1
)
WHERE   rnum >= 3 -- [1,3,5,7]: (Rows per page) * ((page number) - 1) + 1
AND     rnum <= 4 -- [2,4,6,8]: (Rows per page) * (page number)

Query result:

5
4