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

Question

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?

score 2 · Answer 1 · edited Jun 28 '15 at 10:44

2

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)

edited Jun 28 '15 at 10:44

marc_s

732,580
175
1,330
1,459

answered Jun 28 '15 at 07:02

BICube

4,451
1
23
44

It works in ssms but tableadapter doesn't recognize it as parameters! – Mohammad Ha Jun 28 '15 at 07:32
1

I never worked with tableadapter but you can start your query from the select and replace the PageNumber and the RowsPerPage with the actual numbers defined at the top if this is the parameters you are referring to. – BICube Jun 28 '15 at 07:36

Mohammad Ha · Accepted Answer · 2015-07-02T06:50:17.270

0

~~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.

edited Jul 02 '15 at 06:50

answered Jun 28 '15 at 09:33

Mohammad Ha

81
2
9

If that worked it was by luck. select top(@count2) id from Items_table is arbitrary rows – paparazzo Jun 28 '15 at 23:39
You need to add the same order by statement from the outer query to the inner query. Or as Blam mentioned, you might get the results correct only by sheer luck. So it may work now, but it won't always work. – Stephan Jun 29 '15 at 15:49
Yes you are right. I have added an update to the answer and used different method since first one had bugs and performance issues – Mohammad Ha Jul 02 '15 at 06:52

score 0 · Answer 3 · answered Jun 28 '15 at 13:54

0

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

answered Jun 28 '15 at 13:54

naser daneshi

284
1
10

Thank you but the problem is we cannot use declaration in table adapters – Mohammad Ha Jul 02 '15 at 06:51
You dont need to declare variable. They can be the list og parameters in a procedure or function. – naser daneshi Jul 04 '15 at 07:03
Yes if I use stored procedure the problem is solved but with tableadapter query it isn't because it doesn't accept parameters in this mode. Thanks anyway, I used the query in the answered post. – Mohammad Ha Jul 04 '15 at 07:11

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

3 Answers3