Subject Re: order-by problem
Author martinknappe
Hi Adam,
I've uploaded a demo database to demonstrate my issue under the
following URL:

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?

Thanx for your effort,


--- In, "Adam" <s3057043@...> wrote:
> Martin,
> I have now had a brief chance to play subsititute with your tables and
> fields for tables and fields in my database, and the order by works as
> I expected.
> Two things to note:
> 1) By default, the order by will put all the upper case characters first
> 2) If your varchar field contains data that could be interpreted as a
> number, then the sorting rules are different in the varchar field. The
> characters are compared 1 at a time. When 10 is compared to 9, 1 (the
> first character in 10) comes before 9, so it would go first.
> If these are not your issue, then please provide a script to be run in
> iSQL that
> 1) Creates tables
> 2) Inserts some data
> 3) Defines your stored procedure
> 4) Executes that stored procedure, demonstrating the problem.
> You should highlight in step 4 your expected results (just in case we
> don't understand what is wrong).
> We will then be able to determine if it is a bug, a 'feature', or any
> other explanation.