Friday, February 13, 2009

Pagging as concept part2

In addition to my previous post I would like to add that select presented in my previous post was just to demonstrate the concept. In reality select would look something like (in Oracle):

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

Post a Comment