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