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 Johansen
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
|||> 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:

> 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
>
> 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...
>
|||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...
>
>
|||> 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...[vbcol=seagreen]
> 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:

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

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.
I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.
|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.