Non vendor specific paging query

As we know all database vendors deal pagination of result accordingly their product specification

Example:

  • MS-SQL 2000, 2005, 2008 use top, row_number() keywords
  • MySQL & Postgres use limit , offset keywords
  • Oracle uses rownum
  • etc…
Question: Can we create a common paging query run on all vendors available in the market?
Answer is yes.
Explanation:

Example:

If we have table T1 fields id , t1, t2 ,t3 , t4, t5

 

Without Filter Clause


SELECT  * FROM

( SELECT
(SELECT  COUNT(id)  FROM test  WHERE id <= A.id)   AS index1, t1, t2, t3, t4, t5
FROM  T1  AS  A)  AS  result

WHERE result.index1 >= 1  AND  result.index1<=5

 

With Filter Clause


SELECT * FROM

( SELECT
(SELECT  COUNT(id)  FROM test  WHERE id <= A.id)   AS index1, t1, t2, t3, t4, t5
FROM T1  AS  A  WHERE A.t1 = false AND A.t2 > 200

)  AS  result

WHERE  result.index1 >= 1  AND  result.index1<=5


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s