Ok, thanx Adam,
I only thought that because the entries where ordered by asterm
descending, id descending when I picked them in the procedure, a simple
order by asterm ascending, id ascending
would revert that order. Dunno if you follow my apparently wrong logic
but I still can't see why that's wrong...

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "martinknappe" <martin@>
> wrote:
> >
> > Hi Adam,
> > I've uploaded a demo database to demonstrate my issue under the
> > following URL:
> >
> > http://www.usa-host.us/download.php?id=5E842431
> >
> > If you do
> >
> > select asterm, id from dicentries order by asterm ascending, id
> ascending
> >
> > you get all the entries in the table dicentries ordered by asterm
> > ascending, id ascending
> >
> > Now pick one of the records in order to have get_prior_20 have you
> > show the 20 records, that come immediately before that record, say
> > record with id = 47
> >
> > In order to invoke the sp, you would do
> >
> > select * from get_prior_20('Autowaschanlage, f', 47)
> >
> > Now my problem is, I simply want to revert the order in which this sp
> > returns the entries; so I did:
> >
> > select * from get_prior_20('Autowaschanlage, f', 47) order by
> > astermout ascending, idout ascending
> >
> > The order has changed, but it's not the converse of it!
> >
> > Now seeing my point?
>
> You haven't asked it to reverse the order of the stored procedure. You
> asked it to sort by astermout (in ascending order), and if two or more
> records have an identical astermout value, then the lowest idout goes
> first.
>
> I have run the stored procedure, and the records are correctly sorted
> in the way I would expect from your order by.
>
> The output is below (it wraps)
>
> <output>
> Automatik-Abisolierzange, f
> 23
> Automatik-Sicherheitsgurt, m
> 189
> Automatikantenne, f
> 187
> Automatikgetriebe, n
> 14
> Automatikgurt, m
> 188
> Automatische Umluft Control, f
> 27
> Automobilklebstoff, m
> 31
> Autopolitur, f
> 32
> Autoradio, n
> 36
> Autostaubsauger, m (1)
> 38
> Autostaubsauger, m (2)
> 39
> Autowachs, n
> 40
> Autowaschanlage, f
> 47
> automatisch zuschaltender Allradantrieb, m
> 29
> automatische Abisolierzange, f
> 22
> automatische Bremsanlage, f
> 25
> automatische UKW-Stör-Unterdrückung, f
> 26
> automatische Umluftregelung, f
> 28
> automatischer Blockierverhinderer, m ABV
> 149
> automatisches Getriebe, n
> 16
> </output>
>
> If you wanted to simply reverse the order from the stored procedure,
> your stored procedure needs to return a sequential number for each
> record. The easiest way to do this would be to change cnt from a
> variable to an returns parameter, then order by cnt desc.
>
> Also, you should use colons whenever referencing a variable in an into
> statement or comparison (basically anywhere other than an assignment)
>
> eg
>
> CREATE OR ALTER PROCEDURE GET_PRIOR_20 (
> ASTERMIN VARCHAR(80) CHARACTER SET WIN1252,
> IDIN BIGINT)
> RETURNS (
> ASTERMOUT VARCHAR(80) CHARACTER SET WIN1252,
> IDOUT BIGINT,
> CNT INTEGER)
> AS
> begin
> CNT = 0;
> 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 you can see that
>
> select * from get_prior_20('Autowaschanlage, f', 47) returns the cnt
> for each record.
>
> select * from get_prior_20('Autowaschanlage, f', 47) order by cnt desc
>
> will reverse this order.
>
> Adam
>