Cari Blog Ini

23 Agustus 2013

Query Paging Using SQL Server

Apply To :
* Microsoft SQL Server 2000





1. Example Table


2. Logic for code, example here,
---------------------------------------------------------------------------
---------------------------------------------------------------------------
/*
For First Page :
$limit = 2;
SELECT TOP $limit *  FROM aJournal
*/
---------------------------------------------------------------------------
---------------------------------------------------------------------------
/*
For Next Page :
$limit = 2;
$offset = 4;

SELECT TOP $limit *
            FROM aJournal
            where ANO NOT IN
(
           SELECT TOP $offset ANO
           FROM aJournal
           ORDER BY ANO asc
            )
            ORDER BY ANO asc
*/
---------------------------------------------------------------------------
---------------------------------------------------------------------------




3. Try to check of display record in First Page ( Page 1 ),
---------------------------------------------------------------------------
---------------------------------------------------------------------------
SELECT TOP 2 *  FROM aJournal
---------------------------------------------------------------------------
---------------------------------------------------------------------------



4. Try to check of display record in Page 2,
---------------------------------------------------------------------------
---------------------------------------------------------------------------
SELECT TOP 2 *
            FROM aJournal
            where ANO NOT IN
(
           SELECT TOP 4 ANO
           FROM aJournal
           ORDER BY ANO asc
            )
            ORDER BY ANO asc
---------------------------------------------------------------------------
---------------------------------------------------------------------------



5. Try to check of display record in Page 3,
---------------------------------------------------------------------------
---------------------------------------------------------------------------
SELECT TOP 2 *
            FROM aJournal
            where ANO NOT IN
(
           SELECT TOP 6 ANO
           FROM aJournal
           ORDER BY ANO asc
            )
            ORDER BY ANO asc
---------------------------------------------------------------------------
---------------------------------------------------------------------------