--- In firebird-support@yahoogroups.com, "martinknappe" <martin@...>
wrote:

>
> well then again, my question: why does the following NOT work as it
> should:
>
> imagine a table dicentries with (amongst others) 2 fields: asterm and
> id; now imagine a dbgrid with 20 records in it
>
> "order by asterm ascending, id ascending"
>
> when the user scrolls upward (via page up), the previous 20 records
> (according to the same ordering) are to be fetched from the table;
> this is done via reading asterm and id from the uppermost record in
> the grid and handing these two over to an sp called get_prior_20
>
> this sp looks as follows:
>
> CREATE PROCEDURE GET_PRIOR_20 (
> ASTERMIN VARCHAR(80) CHARACTER SET WIN1252,
> IDIN BIGINT)
> RETURNS (
> ASTERMOUT VARCHAR(80) CHARACTER SET WIN1252,
> IDOUT BIGINT)
> AS
> DECLARE VARIABLE CNT INTEGER = 0;
> begin
> for select first 20 id, asterm from
> dicentries
> where asterm = :astermin and id <= :idin
> order by asterm descending, id descending
> into idout, astermout
> do
> begin
> cnt = :cnt + 1;
> suspend;
> end
> for select first 20 id, asterm from
> dicentries
> where asterm < :astermin
> order by asterm descending, id descending
> into idout, astermout
> do
> begin
> if (cnt = 20) then
> exit;
> cnt = :cnt + 1;
> suspend;
> end
> end
>
> now with
>
> select * from get_prior_20('term', 30)
>
> i would get the desired records, only in reverse order than they

should be

> but when i try to revert the order via
>
> select * from get_prior_20('term', 30) order by asterm ascending, id
> ascending
>
> the order of the records is sort of mixed up a bit instead of simply
> reverting it; you see what i mean?

Not really, you have given me a stored procedure from which I would
have to extract out some table definitions and generate some test
data. I have no idea whether the problems are related to the logic, or
whether it is a known issue, or even a problem with WIN1252.

I will hopefully get some time later to write a simple test case. In
the mean time, you may want to search through the tracker to see if
this is a known issue.

Also, remember that ASTERM is a varchar field, so '10' should come
before '9'.

Adam