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

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 JohansenHi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> 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|||> 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.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> 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|||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
"Dan Guzman" wrote:
> > 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.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> > 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
>|||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
"Uri Dimant" wrote:
> Hi
> http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> > 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
>
>|||> 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
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:D2F2F4A6-523F-4333-8DFE-FD4B67E08551@.microsoft.com...
> 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
> "Dan Guzman" wrote:
>> > 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.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote
>> in
>> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>> > 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