How do I get the next or previous rows in a database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kjell Arne Johansen - 05 Mar 2008 11:44 GMT

Hi

I will be greatful for help with the expression.

Example:
'I currently requested 50 rows from a database and now I want the next 50
rows.
I know that the combination of field A, B, C and D makes the row unique.
What should my where clause be?
Is there some other way of doing this?'

select top 50 * from some_table
where <what should this be?>
order by A desc, B desc, C desc, D desc

Regards
Kjell Arne Johansen

Reply to this Message

Uri Dimant - 05 Mar 2008 12:04 GMT

Hi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html

> Hi
>
[quoted text clipped - 13 lines]
> Regards
> Kjell Arne Johansen

Reply to this Message

Kjell Arne Johansen - 05 Mar 2008 13:26 GMT

Thank you very much.

A lot of good examples here.
I forgot to say that I would very much like the solution to work on MS
Access also, so it must probably be some kind of sql expression. -or I have
to make two different solutions for MS Access and SQL Server.

Kjell Arne Johansen
software engineer at Kongsberg Maritime as

> Hi
> http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
[quoted text clipped - 16 lines]
> > Regards
> > Kjell Arne Johansen

Reply to this Message

Dan Guzman - 05 Mar 2008 12:59 GMT

> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?

Below is an example that should work as long as the columns do not allow
nulls.

SELECT TOP 50 *
FROM dbo.some_table
WHERE
A < @LastA
OR (A = @LastA AND B < @LastB)
OR (A = @LastA AND B = @LastB AND C < @LastC)
OR (A = @LastA AND B = @LastB AND C = @LastC AND D < @LastD)
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC

> Is there some other way of doing this?'

Uri provided some other common techniques. In my experience, the key based
pagination method is the fastest and ought to scale linearly regardless of
table size with the appropriate indexes (e.g. conposite primary key or
unique constraint). The downside with key pagination is that indexing can
quickly become complex with user-defined sorting and/or selection criteria.

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi
>
[quoted text clipped - 13 lines]
> Regards
> Kjell Arne Johansen

Reply to this Message

Kjell Arne Johansen - 05 Mar 2008 13:24 GMT

Thank you very much.

It looks to me that this I can use both on MS Access and SQL Server.
If I want to 'page up' then I only change from less than '<' to higher than
'>' I assume.

Thanks.

Kjell Arne Johansen
software engineer at Kongsberg Maritime as

> > I know that the combination of field A, B, C and D makes the row unique.
> > What should my where clause be?
[quoted text clipped - 40 lines]
> > Regards
> > Kjell Arne Johansen

Reply to this Message

Dan Guzman - 05 Mar 2008 13:50 GMT

> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.

In addition to reversing the operators, you'll need to reverse the ORDER BY
so that TOP returns the previous page rows. You will also need wrap this
query in a derived table so that you can order the previous page data in the
original sequence (DESC):

SELECT *
FROM (
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A > @LastA
OR (A = @LastA AND B > @LastB)
OR (A = @LastA AND B = @LastB AND C > @LastC)
OR (A = @LastA AND B = @LastB AND C = @LastC AND D > @LastD)
ORDER BY
A ASC,
B ASC,
C ASC,
D ASC
) AS previous_page
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Thank you very much.
>
[quoted text clipped - 59 lines]
>> > Regards
>> > Kjell Arne Johansen

Reply to this Message