How to implement paging mechanism in generic way for SQL standards?

Question

In a project, we have successfully implemented the paging mechanism for SQL Server, but as per our requirement, we are trying to implement the paging mechanism in generic way for Oracle, SQL Server, DB2 & DB400 databases. Is it possible to implement paging in such way? What can be the simplest generic way to implement such query?

possible duplicate of: http://stackoverflow.com/questions/463859/there-are-a-method-to-paging-using-ansi-sql-only — Tanner, May 14 '15 at 08:49

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

As far as I know there is no generic functionality to implement the pagining mechanism for the all the database.

The syntax to implement the pagination may also change with the database, so it is hard to say that there is a genric functionality to implement it across all the database.

You can refer There are a method to paging using ANSI Sql only? where the accepted answer refers to a link which says to use it like

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum,
    columns
  FROM tablename
) AS foo
WHERE rownum > skip AND rownum <= (n+skip)

edited May 23 '17 at 12:30

Community

1
1

answered May 14 '15 at 09:08

Rahul Tripathi

168,305
31
280
331

Thanks for your opinion, Does Oracle,DB2,DB400 support CTE syntax i.e. Common Table Expression ? – FullStack May 14 '15 at 09:12
@MaximusDecimusMeridius:- Yes you can use it. For Oracle you can refer this: https://community.oracle.com/thread/921467?tstart=0 – Rahul Tripathi May 14 '15 at 09:14
Thank you,MS-SQL server,Oracle,DB2,DB400 supports CTE, that means the paging can be implemented using few of the mathematical calculations. Atleast we can create generic query for these 4 databases,Am i correct ? – FullStack May 14 '15 at 09:18
@MaximusDecimusMeridius:- Yes, with few calculations it can be done! – Rahul Tripathi May 14 '15 at 09:19
Thanks Rahul,i will get back to you in case of difficulties. – FullStack May 14 '15 at 09:20
@MaximusDecimusMeridius:- You are welcome! Surely, I will wait! – Rahul Tripathi May 14 '15 at 09:21
I have the scenario like,while moving back & forth to display the records as per the row numbers per page there is possibility that new set of records might have been added in the database & the page number we are sending might fail, so will it work to display the data properly in paging ? – FullStack May 15 '15 at 04:44

score 0 · Answer 2 · edited May 14 '15 at 11:44

0

What I understand. Try this:

WITH MyCte AS 
(
    SELECT row_number() OVER (ORDER BY FieldName) ID, *  FROM TableName
)
SELECT  *
FROM    MyCte
WHERE   ID BETWEEN 1 AND 10

edited May 14 '15 at 11:44

shA.t

16,580
5
54
111

answered May 14 '15 at 09:01

ISB

112
7

Will it be okay with Oracle,DB2,DB400 ? I mean the syntax provided by you ? – FullStack May 14 '15 at 09:05
I am not sure about oracle. Might some functions not available with same name like CTE, Row_Number. But the logic should be same. You need to check the functions equivalent in Oracle. If CTE not available yo can use sub query. Inner Select statement Add Serial Number and from Outer Select statement you can put check according to the page number. Thanks – ISB May 14 '15 at 10:20

score 0 · Answer 3 · answered May 14 '15 at 09:22

In Oracle, there are multiple ways:

ROWNUM in subquery and ORDER BY in outer query
Analytic ROW_NUMBER()
TOP-n row limiting clause in new Oracle 12c

Using ROWNUM:

SQL> WITH DATA AS(
  2  SELECT level col
  3  FROM   dual
  4  CONNECT BY LEVEL <= 10
  5  )
  6  SELECT col
  7  FROM   (SELECT col, rownum AS rnum
  8          FROM   (SELECT col
  9                  FROM   data
 10                  ORDER BY col)
 11          WHERE rownum <= 8)
 12  WHERE  rnum >= 5;

       COL
----------
         5
         6
         7
         8

Using Analytic ROW_NUMBER():

SQL> WITH DATA AS(
  2  SELECT level col
  3  FROM   dual
  4  CONNECT BY LEVEL <= 10
  5  )
  6  SELECT * FROM (
  7    SELECT
  8      ROW_NUMBER() OVER (ORDER BY col) rn
  9    FROM DATA
 10  )
 11  WHERE rn >= 5 AND rn <= 8;

        RN
----------
         5
         6
         7
         8

Using Top-n row limiting clause:

SQL> WITH DATA AS(
  2  SELECT level col
  3  FROM   dual
  4  CONNECT BY LEVEL <= 10
  5  )
  6  SELECT col
  7  FROM   DATA
  8  ORDER BY col
  9  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       COL
----------
         5
         6
         7
         8

How to implement paging mechanism in generic way for SQL standards?

3 Answers3