db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: ORDER BY and greek characters
Date Mon, 01 Dec 2008 15:39:09 GMT
tom_ wrote:
> I'm using ORDER BY and a database with collation territory based de_DE.
> Strings starting with greek characters are at the end of the resultset.
> Users would expect them near the respective latin character, e.g. greek
> "alpha" near "a". Is there a possiblity to sort in this way?
Hi Tom,

Here a couple suggestions:

1) You can write your own public static method which takes a String 
argument and returns a byte array, where the byte array sorts in the 
order that you want--note that Derby sorts byte arrays 
lexicographically, just like Strings. The existing support for 
CollationKey should help you write this method. Your method's signature 
would look like this:

  public static byte[] myOrder( String raw ) throws SQLException { ... }

Then you would register your method as a Derby function:

create function myOrder
    raw varchar( 100 )
returns varchar( 1000 ) for bit data
language java
parameter style java
no sql
external name 'MyClass.myOrder'

Finally, you would use this function in queries like this:

select * from t order by myOrder( a )

+ The good thing about this solution is that it should work today on 10.4.
- The downside is that you don't have indexing support for these queries.

2) You could change Derby so that it supports user-defined Locales with 
custom collations. I haven't looked into the details here, but this 
seems like a smallish task--gated by a community discussion about how to 
declare these user-created Locales.

+ This would be generally useful.
+ Indexes would work for you and give your queries better performance.
- This solution wouldn't hit production until Derby 10.5 at the earliest.

3) You could build on solution (1) and use generated columns. As in (1), 
you would write the myOrder function. Your would register this method as 
a deterministic function:

create function myOrder
    raw varchar( 100 )
returns varchar( 1000 ) for bit data
language java
parameter style java
no sql
external name 'z.myOrder'

Then you would declare and index your table like this:

create table t( a varchar( 100 ), b generated always as ( myOrder( a ) ) )
create index t_idx on t( b )

Finally, you would query your table like so:

select a from t order by b

+ This would give you indexing support as in (2).
- Generated columns won't be available until the next feature release, 10.5

Let me know if you have more questions about these solutions.

Hope this helps,

View raw message