Prev: Using row_number() in Recurcive CTE
Next: Populate matching Data from another column into one
From: Hemant on 16 Jun 2010 10:26
Hi,

I have to select record on the basis of page number and page size from
Database.
I am working on SQL server 2000.
In select query sorting and searching included.

Please help me.

Thanks,
Hemant


From: Iain Sharp on 16 Jun 2010 13:11
On Wed, 16 Jun 2010 19:56:05 +0530, "Hemant" <Hemant(a)nomail.com>
wrote:

>Hi,
>
>I have to select record on the basis of page number and page size from
>Database.
>I am working on SQL server 2000.
>In select query sorting and searching included.
>
>Please help me.
>
>Thanks,
>Hemant
>

Okay, you could try this.

select * from (
select top page_size * from
( select top page_no*page_size <your fields>
<your current from>
<your current where>
<your current order by>
) as first_x_pages
order by <your current order by reversed>) as page
order by <your current order by>

So
select * from (
select top (20) * from
( select top (3*20) foo_code, foo_desc
from foo
where foo_code like 'A%'
order by foo_code
) as first_x_pages
order by foo_code desc) as page
order by foo_code


Alternatively, use

select top XX etc...
and include where foo_code > <end_of_last_page from the application>

Iain


Iain
 | 
Pages: 1
Prev: Using row_number() in Recurcive CTE
Next: Populate matching Data from another column into one