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?

thanx,

martin

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "martinknappe" <martin@>
> wrote:
> >
> > ok, so i figure a table that's created as the result of an sp can't be
> > ordered by anything...but is there no way to simply REVERSE the order
> > of the entries in a result table once they've all been suspended?
>
> Simply not true on several records.
>
> 1. Unless you issue a create table statement inside an execute
> statement directive, you can not 'create a table as the result of an
> sp'. I assume you mean a resultset / recordset / dataset.
>
> 2. You can include anything returned by a stored procedure through the
> suspend statement using the stock standard order by statement. eg
>
> select ID
> from MyProc
> order by ID desc
>
> Of course, the entire recordset will need to be gone through before
> you get your first record, so I wouldnt be trying such logic with 100
> million records, but you can probably get away with a couple of hundred.
>
> Adam
>