sql query selection

Question

I am trying to build a query which will do this: Lets say for example I have 100 records in the table. I have .net form which calls the query. I have a querystring parameter pageindex, something like http://mysite.com?id=2. What I want to do now is if id = NULL, then get the 1st set of records from that table whichave id of 1 to 20, means from 1 to 20, if id=2 then get the 2nd set of recordw, from row 20 to 40, if id=3 then get the 3rd set of records, meaning records from 40 to 60, from that table.
I want to know if this is possible. Thanks a lot in advance, Laziale

What does your current page look like, what does your database code look like now? — Brendan Long, Mar 28 '12 at 23:05
[Paging in SQL Server](http://www.codeguru.com/csharp/.net/net_data/article.php/c19611/Paging-in-SQL-Server-2005.htm) — Michael Fredrickson, Mar 28 '12 at 23:06

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

SELECT col1, col2 
FROM (
    SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
    FROM MyTable
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

taken from Row Offset in SQL Server, first result in google via "mssql limit offset" search queue.

edited May 23 '17 at 12:20

Community

1
1

answered Mar 28 '12 at 23:12

iehrlich

3,572
4
34
43

The question is not asking about MySQL. How do you do that in SQL Server? – Mosty Mostacho Mar 28 '12 at 23:19

Aristos · Accepted Answer · 2012-03-28T23:53:29.167

If you make it as you say so, and the IDs are in line from 1 to 100 you can do

@Page is the page number (base on 0)

SELECT TOP 20 * FROM MyTable WHERE (ID > @Page*20) ORDER BY ID

If you wish to use the paging style of Ms SQL and the ids are not in line you can do

WITH NewTable AS (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber FROM MyTable)
SELECT TOP 20 * FROM NewTable WHERE (RowNumber > @Page*20)

Reference : http://msdn.microsoft.com/en-us/library/ms186734.aspx

http://msdn.microsoft.com/en-us/library/ms175972.aspx

sql query selection

2 Answers2