Question Re: paging in sql server in Technology Q&A

paging in sql server

Former Member — Thu, 05 Apr 2012 00:33:25 GMT

I am new to sql and I am in trouble of fetching data from sql server using paging method.

I have one table having Primary column,DateModified and other fields. table having 4lac records.

I want to implement paging using sql server.

Conditions: 1] First column must be row number. 2] Latest modified records must be come first using DateModified columns 3] Perfomance must be high.

I have problem in query.

Suppose, I use

select * from (Select row_number() over(order by SoldProductId) as rowNum,SoldProductId, OtherId,DateModified From table1 order by DateModified desc ) as TempTable where rowNum between 1 and 10

query. Then due to order by clause this query doest work.

Suppose, I use

select * from (Select row_number() over(order by SoldProductId) as rowNum,SoldProductId, OtherId,DateModified From table1 ) as TempTable where rowNum between 1 and 10

query.It doesnt returns latest records first.

How Can I solve this problem.

Please Help!!!

Re: paging in sql server

VolkerBarth — Thu, 05 Apr 2012 03:28:17 GMT

This is a forum on Sybase SQL Anywhere (formerly named Sybase Adaptive Server Anywhere). If you're asking for Microsoft SQL Server, I would recommend a different forum/newsgroup.

Confine the FAQ.

Re: paging in sql server

jack_schueler — Thu, 05 Apr 2012 11:50:24 GMT

Assuming that you really are using SQL Anywhere, it might help if you provided an example of what you would like to see as the result set. For example, based on your description of the table, I picture data like this:

pkey, DateModified, SoldProductId, OtherId

1, '2012/03/03', 1000, 2000
2, '2012/03/03', 1001, 2000
3, '2012/03/04', 1002, 2000
4, '2012/03/05', 1003, 2000
5, '2012/03/05', 1000, 2000
6, '2012/03/06', 1001, 2000
7, '2012/03/06', 1002, 2000
8, '2012/03/07', 1003, 2000
9, '2012/03/08', 1000, 2000
10, '2012/03/09', 1001, 2000
11, '2012/03/10', 1002, 2000
12, '2012/03/11', 1003, 2000
13, '2012/03/12', 1000, 2000
14, '2012/03/13', 1001, 2000
15, '2012/03/14', 1002, 2000
16, '2012/03/15', 1000, 2000
17, '2012/03/15', 1000, 2000
18, '2012/03/16', 1001, 2000
19, '2012/03/16', 1000, 2000
20, '2012/03/17', 1001, 2000
21, '2012/03/18', 1000, 2000
22, '2012/03/19', 1001, 2000
23, '2012/03/20', 1001, 2000

So how you want this sorted (i.e., what does your result set look like)?

Your first query produces this result which I understand is not what you want.

rowNum,SoldProductId,OtherId,DateModified
8,1000,2000,'2012/03/18'
7,1000,2000,'2012/03/16'
5,1000,2000,'2012/03/15'
6,1000,2000,'2012/03/15'
4,1000,2000,'2012/03/12'
3,1000,2000,'2012/03/08'
10,1001,2000,'2012/03/06'
2,1000,2000,'2012/03/05'
1,1000,2000,'2012/03/03'
9,1001,2000,'2012/03/03'