SELECT *
FROM (SELECT ROWNUM row_num, column1, column2
FROM (SELECT column1, column2
FROM table
)
WHERE ROWNUM <= :page_num * :rec_count)
WHERE row_num > (:page_num - 1) * :rec_count
Rec_count tells us how many records we want on a page, an page_num is the number of page we want.
I like this version more that maybe more used version:
SELECT *
FROM (SELECT ROWNUM, table_name.*
FROM (SELECT *
FROM table) table_name
WHERE ROWNUM <= 200)
WHERE ROWNUM <= 101
Whole point of doing sub select is to limit the number of results with inner select and then extract the rows required with outer select.
Simple implementation of SQL for paging modification would look something like:
Public Function GetSQL(ByVal sqlin As String, ByVal sort As String, ByVal table As String, ByVal page As Integer, ByVal rec_count As Integer) As String
Dim sql2 As String = " SELECT * FROM (SELECT ROWNUM as ROW_NUM, " & table & ".* FROM "
sql2 = sql2 & " ( " & sqlin & " " & sort & " ) " & table
sql2 = sql2 & " WHERE ROWNUM <= " & page & " * " & rec_count & ") WHERE ROW_NUM > (" & page & " - 1) *" & rec_count
Return sql2
End Function
No comments:
Post a Comment