Pagination

use tempdb database

USE tempdb;

create temporary table

IF OBJECT_ID(‘tempdb.dbo.#sampletable’;) IS NOT NULL DROP TABLE #sampletable
CREATE TABLE #sampletable (
id INT IDENTITY PRIMARY KEY,
col1 VARCHAR(10),
col2 VARCHAR(10),
col3 VARCHAR(10),
col4 VARCHAR(10),
col5 VARCHAR(10)
);

insert dummy data

INSERT INTO #sampletable (col1, col2, col3, col4, col5) VALUES (‘row11′,’row12′,’row13′,’row14′,’row15’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row21′,’row22′,’row23′,’row24′,’row25’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row31′,’row32′,’row33′,’row34′,’row35’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row41′,’row42′,’row43′,’row44′,’row45’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row51′,’row52′,’row53′,’row54′,’row55’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row61′,’row62′,’row63′,’row64′,’row65’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row71′,’row72′,’row73′,’row74′,’row75’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row81′,’row82′,’row83′,’row84′,’row85’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row91′,’row92′,’row93′,’row94′,’row95’;);
INSERT INTO #sampletable(col1, col2, col3, col4, col5) VALUES (‘row01′,’row02′,’row03′,’row04′,’row05’;);

pagination query

WITH pagination AS (
SELECT ROW_NUMBER () OVER (ORDER BY id) as RowID, *
FROM dbo.#sampletable
)
SELECT * FROM pagination WHERE RowID BETWEEN 2 and 3

– marifnst –

Leave a Reply

Your email address will not be published. Required fields are marked *

Afiseaza emoticoanele Locco.Ro