Re: Custom sorting in ORDER BY
On 02/02/2023 17:13, Rick Hillegas wrote: I can think of two solutions to your problem: 1) Write a custom Java collator and install it as the default sort order for all string data in your database. See the following sections in the Derby Developer's Guide: https://db.apache.org/derby/docs/10.16/devguide/cdevcollation.html and https://db.apache.org/derby/docs/10.16/devguide/tdevdvlpcustomcollation.html#tdevdvlpcustomcollation 2) The second approach, as you suggested, is to create a Derby function which recodes string types into some other type like VARBINARY. The function would need to divide the string into its alphabetic and numeric components and then reassemble them in a binary order which sorts the way you want. Your query would then look like: SELECT ... ORDER BY (recodingFunction(alphanumericColumn)) There are a lot of fiddly subtleties to both approaches. I can't give more advice because I haven't tackled this problem myself. Thanks, Rick. I can't see how I would do method 2, as I want 'foo5' to come before 'foo10', but I also want 'x1' to come after both, and since the numbers aren't in the same place (or there might be more than one numeric part -- 'Section 5 para 10", for example) I don't see how to do this except of I have two values to compare. I'll look at the DB and think about what method 1 might affect, since it would apply to the entire DB. Off the top of my head, I can't think of any problems, but the devil is in the detail. It's a pity that there isn't a way to specify a collation order other than ASC/DESC in individual queries. Oh well. Thanks again, -- John English
Re: Custom sorting in ORDER BY
I can think of two solutions to your problem: 1) Write a custom Java collator and install it as the default sort order for all string data in your database. See the following sections in the Derby Developer's Guide: https://db.apache.org/derby/docs/10.16/devguide/cdevcollation.html and https://db.apache.org/derby/docs/10.16/devguide/tdevdvlpcustomcollation.html#tdevdvlpcustomcollation 2) The second approach, as you suggested, is to create a Derby function which recodes string types into some other type like VARBINARY. The function would need to divide the string into its alphabetic and numeric components and then reassemble them in a binary order which sorts the way you want. Your query would then look like: SELECT ... ORDER BY (recodingFunction(alphanumericColumn)) There are a lot of fiddly subtleties to both approaches. I can't give more advice because I haven't tackled this problem myself. Hope this helps, -Rick On 2/2/23 12:35 AM, John English wrote: I would like to be able to specify an ORDER BY which will give me the same sort of ordering as produced by my file browser, where embedded numbers in a string with the same prefix are ordered numerically. For example: if I have strings 'x1','foo1','x2','foo10','foo5', a normal text sort will give 'foo1','foo10','foo5','x1','x2' (that is, 'foo10' will come before 'foo5'). I would like to be able to produce the order 'foo1','foo5','foo10','x1','x2' instead. I could do this in Java by defining a custom comparator, and then import this for use in Derby with CREATE FUNCTION but how would I then use it in an ORDER BY clause to give me the desired sort order? Thanks for any tips,
Custom sorting in ORDER BY
I would like to be able to specify an ORDER BY which will give me the same sort of ordering as produced by my file browser, where embedded numbers in a string with the same prefix are ordered numerically. For example: if I have strings 'x1','foo1','x2','foo10','foo5', a normal text sort will give 'foo1','foo10','foo5','x1','x2' (that is, 'foo10' will come before 'foo5'). I would like to be able to produce the order 'foo1','foo5','foo10','x1','x2' instead. I could do this in Java by defining a custom comparator, and then import this for use in Derby with CREATE FUNCTION but how would I then use it in an ORDER BY clause to give me the desired sort order? Thanks for any tips, -- John English
