Paged query statements that are 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 > (current Page*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 >= (cur Rentpage*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 (order by sort with column name ASC) 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. Version 3 supports top queries, or uses SET ROWCOUNT N to query header n data
A temporary table is also used:
Select Rowid=identity, column_name to #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
Different database Oracle MySQL SQL Server DB2 infomix Sybase paging query statement