Paging query statements for different databases OracleMySQLSQLServerDB2infomixsybase

Paging query statements for different databases OracleMySQLSQLServerDB2infomixsybase

Oracle MySQL SQL Server DB2 infomix sybase paging query statements for different databases

Paging query statements used in different databases:

Current page: currentpage
Page size: pagesize

1. Oracle Database

Select * from (select A. *, rownum rn from (QUERY_ SQL) A) where rn <= (currentpage + 1) * pagesize) and rn> (currentpage * pagesize)

Note: QUERY_ SQL is a query SQL statement.

Or

Select * from (select rownum rn, id from TABLENAME where rownum <= (currentpage + 1) * pagesize) A where A. rn> = (currentpage * pagesize)

2. Infomix Database

Select skip currentpage first pagesize * from TABLENAME

3. DB2 database

Select * from (select Field 1, Field 2, Field 3, rownumber () over (column name asc used for order by sorting) as RN from table name) as A1 where A1.RN between (currentpage * pagesize) and (currentpage + 1) * pagesize)
Or
Select * from (select rownumber () over (order by id asc) as rowid from table where rowid <= (currentpage + 1) * pagesize )) where rowid> (currentpage * pagesize)


4. SQL Server database

Select top pagesize *
From TABLENAME
Where COLLUMN_NO not in
(Select top currentpage * pagesize COLLUMN_NO from TABLENAME order by COLLUMN_NO)
Order by COLLUMN_NO

5. Sybase Database

Sybase 12.5.3 Supports top query, or use set rowcount N to query the first N data entries.
Use temporary tables:
Select rowid = identity (12), column_name into # TEMPTABLE from TABLENAME
Select column_name from # TEMPTABLE where rowid> (currentpage * pagesize) and rowid <(currentpage * pagesize + pagesize)

6. MySQL database

SELECT * FROM TABLE1 LIMIT (currentpage * pagesize), pagesize