Paging

Sql server

Assume:

@PageSize is the row counts displayed on page

@CurrentPage is the page number

SELECT TOP @PageSize * FROM Table WHERE ID NOT IN
    (SELECT TOP @PageSize*@CurrentPage ID FROM Table ORDER BY ID DESC)
ORDER BY ID DESC

Oracle

Assume:

@MaxRowNum means the max row to query

@MinRowNum means the min row to query

SELECT * FROM 
    (SELECT rownum, * FROM
        (SELECT * FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
    WHERE rownum <= @MaxRowNum)
WHERE rownum >= @MinRowNum

↑better performance on visit first serval pages

SELECT * FROM Table WHERE rowind IN
    (SELECT rid FROM
        (SELECT rownum rno, rowid rid FROM
            (SELECT rowid FROM Table WHERE SomeColumn = Something ORDER BY SomeColumns)
        WHERE rownum <= @MaxRowNum)
    WHERE rno >= @MinRowNum)

↑better performance on higher page numbers

SELECT * FROM 
    (SELECT owner, table_name, row_number() OVER(ORDER BY table_name) rownumber FROM dba_tables)
WHERE rownumber >= @MinRowNum AND rownumber <= @MaxRowNum