paging in sql server

-1

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!!!

query-help

asked 05 Apr '12, 00:33

Kaushik Halv...
0●1●1●1
accept rate: 0%

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.

(05 Apr '12, 03:28) Volker Barth

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'

(05 Apr '12, 11:50) JBSchueler

flat view

Be the first one to answer this question!

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

query-help ×12

question asked: 05 Apr '12, 00:33

question was seen: 1,771 times

last updated: 05 Apr '12, 13:54

SAP SQL Anywhere

paging in sql server

Follow this question

Related questions