CODEWITHSUNDEEP
asp.netsql-servert-sqltableadapter
Mohammad Ha
Mohammad Ha

Reputation: 81

best t-sql for providing nth page in showing data?

I want to item 1 to 50 in my first page by using 

SELECT TOP(50) * 
FROM Items_table

and it works fine but how can I get second 50 items for next page? How should be the query be?

Please note that I can't use LIMIT because I am using SQL Server.

I have also problem with queries like:

select * 
from (
    select 
        ROW_NUMBER() over (
            ORDER BY date_of_creation desc, time_of_creation desc) AS Row2,
        * 
    from 
        Items_table) ir
where 
    ir.Row2 between @start and @end

The problem is in this condition table adapter doesn't support OVER.

Is there any other t-sql code that table adapter support?

Upvotes: 2

Views: 78

Answers (3)

Mohammad Ha
Mohammad Ha

Reputation: 81

I've achieved that with this query in table adapters:

select top (@count) * 
from Items_table
where id not in (select top(@count2) id from Items_table)
order by Date_Of_Creation desc,Time_Of_Creation desc

Thanks for participating.

Update: Please don't use that either with ordering right in inner query because I had repeated results and some bugs. In addition when you want load 10th page the performance of query will be bad (The time needed to execute query is not acceptable).

Instead I was forced to use this method (and it is good also):

Select TOP (@count) from Item_table
order by Date_Of_Creation desc,Time_Of_Creation desc

Then each page should contain some of the rows of this query(If you are going to use multi page).

If you have one page that when user reaches bottom you want to load more, in this method you should execute this query every time you want to load more with bigger @count and load the items in the page.

Upvotes: 0

naser daneshi
naser daneshi

Reputation: 294

if you wanna code in sql 2008 try this:

    Drop Table T1 ;
GO
Create Table T1( id int, Title varchar(100) );
Insert T1 Values
( 1000, 'A1000' ),( 1001, 'A1001' ),( 1002, 'A1002' ),( 1003, 'A1003' ),( 1004, 'A1004' ),
( 1005, 'A1005' ),( 1006, 'A1006' ),( 1007, 'A1007' ),( 1008, 'A1008' ),( 1009, 'A1009' ),
( 1010, 'A1010' ),( 1011, 'A1011' ),( 1012, 'A1012' ),( 1013, 'A1013' ),( 1014, 'A1014' ),
( 1015, 'A1015' ),( 1016, 'A1016' ),( 1017, 'A1017' ),( 1018, 'A1018' ),( 1019, 'A1019' );
GO


Declare @PageNO int =1, @RowsPerPage int =5;
Select Rw,Id, Title FROM
(
    Select 
        Rw=Row_number() Over( order by Id ) , Id, Title 
    from T1
) A
where Rw between (@PageNO-1)*@RowsPerPage+1 and @PageNO*@RowsPerPage

Upvotes: 0

BICube
BICube

Reputation: 4681

If you are using SQL Server 2012 or above, then this will help you 

DECLARE @RowsPerPage INT = 50; 
DECLARE @PageNumber INT = 2; 

SELECT *
FROM ItemsTable
ORDER BY date_of_creation desc, time_of_creation desc
OFFSET (@PageNumber - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY

Variable @PageNumber specifies the page that you want to retrieve (first, second ..etc)

Upvotes: 2

Related Questions