sqlpaginationprimary-keyoracle-database

Paging results from a table having a varchar as PK


How can I page results from a table with this schema?

CREATE TABLE "MY_TABLE"
(
   "NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
   "STATUS" VARCHAR2(20 BYTE),
   "DESCRIPTION" VARCHAR2(4000 BYTE)
   CONSTRAINT "MY_TABLE_PK" PRIMARY KEY ("NAME")
)

Solution

  • Basing my answer off the suggestion in the question comments, this should work for your table.

    SELECT r.Name, r.Status. r.Description
    FROM
    (
        SELECT t.Name, t.Status. t.Description, rownum RowNumber
        FROM
        (
            SELECT Name, Status, Description
            FROM YourTable
            ORDER BY Name ASC
        ) AS t
        WHERE rownum < (pageNumber * pageSize) + 1
    ) AS r
    WHERE RowNumber >= ((pageNumber - 1) * pageSize) + 1
    

    A good explanation of what rownum does can be found on AskTom.Oracle.com