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

>
> > You seem to be expecting a case insensitive sort, which you will not
> > get unless you define such a collation or use the upper.
>
> Not at all. All I was expecting is that doing "order by asterm
> ascending, id ascending" on something that's "ordered by asterm
> descending, id descending" would revert the order. I might e.g. have
> an ordered-by-asterm-ascending,-id-ascending dataset like the following
>
> asterm id
> a 1
> a 2
> b 3
> b 5
> b 7
> c 4
> c 8
>
> an order by asterm descending, id descending on that dataset should
> return the converse order no matter what collation im using,

shouldn't it?

>

I am using your stored procedure here and the original example:

select * from get_prior_20('Autowaschanlage, f', 47);

Inside the stored procedure, the first query to be run is:

for select first 20 id, asterm from
dicentries
where asterm = :astermin and id <= :idin
order by asterm descending, id descending

In this case, the record returned is (there is only 1)

ID ASTERM

======== ===============================================
47 Autowaschanlage, f


Suspend is called once, returning this value

The next query you run is

select first 20 id, asterm from
dicentries
where asterm < :astermin
order by asterm descending, id descending

In this case, there are 20 records returned, but due to your logic
inside your for loop, cnt = 20 will cause it to exit before it returns
the final record.

ID ASTERM

======== ================================================
40 Autowachs, n

39 Autostaubsauger, m (2)

38 Autostaubsauger, m (1)

36 Autoradio, n

32 Autopolitur, f

31 Automobilklebstoff, m

16 automatisches Getriebe, n

149 automatischer Blockierverhinderer, m ABV

28 automatische Umluftregelung, f

27 Automatische Umluft Control, f

26 automatische UKW-Stör-Unterdrückung, f

25 automatische Bremsanlage, f

22 automatische Abisolierzange, f

29 automatisch zuschaltender Allradantrieb, m

188 Automatikgurt, m

14 Automatikgetriebe, n

187 Automatikantenne, f

189 Automatik-Sicherheitsgurt, m

23 Automatik-Abisolierzange, f

12 Automatik, m (2)


So, your stored procedure without an order by returns the records:

ID ASTERM

======== ===========================================================
47 Autowaschanlage, f

40 Autowachs, n

39 Autostaubsauger, m (2)

38 Autostaubsauger, m (1)

36 Autoradio, n

32 Autopolitur, f

31 Automobilklebstoff, m

16 automatisches Getriebe, n

149 automatischer Blockierverhinderer, m ABV

28 automatische Umluftregelung, f

27 Automatische Umluft Control, f

26 automatische UKW-Stör-Unterdrückung, f

25 automatische Bremsanlage, f

22 automatische Abisolierzange, f

29 automatisch zuschaltender Allradantrieb, m

188 Automatikgurt, m

14 Automatikgetriebe, n

187 Automatikantenne, f

189 Automatik-Sicherheitsgurt, m

23 Automatik-Abisolierzange, f


Further investigation shows that you *** DO *** use a collation, and
as I told you before, Collations can allow for case insensitive sorting.

dicentries.ASTERM is defined as a field of type S_CHAR_80

This is a domain declared by you (or someone you work with) as follows:

CREATE DOMAIN T_CHAR_80 AS VARCHAR(80) COLLATE PXW_INTL850;

That means that the two internal queries of the stored procedure sort
in a case insensitive fashion.

When you change your original query to:

select * from get_prior_20('Autowaschanlage, f', 47) order by
astermout ascending, idout ascending

You are ordering by ASTERMOUT (the output parameter of the stored
procedure), not by the field you happen to use inside the stored
procedure to assign values to this parameter. There is no reason you
can not output a fixed string or some other field to that parameter if
you had wanted to (providing it of course fits inside a varchar(80)).

The parameter is defined as

ASTERMOUT VARCHAR(80)

This does not have a collation like your table, so it will order the
results case sensitive! I have not seen any syntax to assign a
collation to an output variable, but you can define one in your select.

To compare apples with apples, I would expect the following query to
return it in reverse order.

select * from get_prior_20('Autowaschanlage, f', 47) order by
astermout COLLATE PXW_INTL850 ascending, idout ascending

But otherwise, you can't expect two queries ordering based on rules of
two different collations to come up in the reverse order because you
switched descending and ascending everywhere. Once they are the same
collation, you can make that assumption.

Adam