Re: ORDER BY 'criteria'

On Thursday 23 January 2003 18:02, Dennis Gearon wrote:
> Scenario:
> A table that has recoreds in every language under the sun in it,
> in UTF-8 format,
> A column which identifies which language a record is in,
> An INT4 column indentifying the items which are stored in
> multiple languages.
>
> Examples:
> CtryID Country Language
> 1 Russia US-EN
> 1 Руссйя RU <russian spelled in Russian, sort of>
>
> I want to pull out groups of records by language and the country, and then
> SORT THE RECORDS IN THE LANGUAGE SELECTED.
>
> Is there any way to say which language to ORDER BY in an SQL statement? Is
> there in the SQL standard?

You mean "is there any way to determine the sort order used by
ORDER BY", i.e. for records defined as Russian and containing Cyrillic UTF-8
characters use Russian sorting order etc.?

The short answer is "no" (I am happy to be proven wrong of course ;-).
As far as PostgreSQL is concerned, the locale used to determine sorting
order is predefined at initdb and can't be changed dynamically.
I don't know what the SQL standards say about this.

There is probably no practical way of doing what you are looking for,
especially when you consider the sorting order of some languages
cannot be algorithmically determined. Japanese for example would
require an extra field containing a phonetic key for each record.
For example, in the following two words the first character is the same,
but the first word is pronounce "Kobe" and the second "Kanda":
神戸
神田
(the first character can also be pronounced "shin" or "kami").

> If not, this is a serious deficiency for global companies.

I suspect global companies maintain local dependencies which make
sure customer lists or whatever are generated in the correct order...

Ian Barwick
barwick(at)gmx(dot)net