You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Ronald Rudy <ro...@gmail.com> on 2010/02/05 14:16:51 UTC

Perhaps a stupid question

Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column?  I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :).  And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ....?

My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable.

Thanks,
   -Ron

Re: Perhaps a stupid question

Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Ron,

A response inline...

Ronald Rudy wrote:
> Interesting, I'll take a look...
>
> In the interest of portability (in case we ever swap out Derby for something else) I think for now I'll use a separate column I build and use specifically for sorting.
>
> On a somewhat related question as it pertains to indexes - if I have this prepared statement (used frequently):
>
> SELECT col0, col1 FROM mytable WHERE col0 = ? AND col1 = ? ORDER BY sortcol
>
> Does it buy me anything to include the "sortcol" in the index along with "col0" and "col1"?  If I should only index "col0" and "col1", does it buy me anything to add a second index with just "sortcol" in it?
>   
If your index includes sortcol as a trailing column, then it is what we 
call a "covering index". This means that the index contains all of the 
columns needed to evaluate the query. This can speed up your query 
because Derby does not have to probe into the base table for extra 
information. So an index like this might help you:

create index myIndex on myTable( col0, col1, sortcol );

Hope this helps,
-Rick
>
> On Feb 5, 2010, at 9:25:00 AM, Rick Hillegas wrote:
>
>   
>> Hi Ron,
>>
>> If what you need is a custom sort order for string data, you can plug in your own custom collator. See Knut's blog for details on how to do this: http://blogs.sun.com/kah/entry/user_defined_collation_in_apache
>>
>> You can also use generated columns to build user-defined ordering. See the following writeup for advice on how to do this: http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns
>>
>> If these solutions don't work for you, tell us more about what you need. We may be able to suggest another solution or at least capture your requirements for a future enhancement.
>>
>> Thanks,
>> -Rick
>>
>>
>> Ronald Rudy wrote:
>>     
>>> Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column?  I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :).  And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ....?
>>>
>>> My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable.
>>>
>>> Thanks,
>>>   -Ron
>>>  
>>>       
>
>

Re: Perhaps a stupid question

Posted by Ronald Rudy <ro...@gmail.com>.
Interesting, I'll take a look...

In the interest of portability (in case we ever swap out Derby for something else) I think for now I'll use a separate column I build and use specifically for sorting.

On a somewhat related question as it pertains to indexes - if I have this prepared statement (used frequently):

SELECT col0, col1 FROM mytable WHERE col0 = ? AND col1 = ? ORDER BY sortcol

Does it buy me anything to include the "sortcol" in the index along with "col0" and "col1"?  If I should only index "col0" and "col1", does it buy me anything to add a second index with just "sortcol" in it?


On Feb 5, 2010, at 9:25:00 AM, Rick Hillegas wrote:

> Hi Ron,
> 
> If what you need is a custom sort order for string data, you can plug in your own custom collator. See Knut's blog for details on how to do this: http://blogs.sun.com/kah/entry/user_defined_collation_in_apache
> 
> You can also use generated columns to build user-defined ordering. See the following writeup for advice on how to do this: http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns
> 
> If these solutions don't work for you, tell us more about what you need. We may be able to suggest another solution or at least capture your requirements for a future enhancement.
> 
> Thanks,
> -Rick
> 
> 
> Ronald Rudy wrote:
>> Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column?  I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :).  And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ....?
>> 
>> My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable.
>> 
>> Thanks,
>>   -Ron
>>  
>

Re: Perhaps a stupid question

Posted by Rick Hillegas <Ri...@Sun.COM>.
Hi Ron,

If what you need is a custom sort order for string data, you can plug in 
your own custom collator. See Knut's blog for details on how to do this: 
http://blogs.sun.com/kah/entry/user_defined_collation_in_apache

You can also use generated columns to build user-defined ordering. See 
the following writeup for advice on how to do this: 
http://java.sun.com/developer/technicalArticles/Database/javadb_10_5/index.html#Generated_Columns

If these solutions don't work for you, tell us more about what you need. 
We may be able to suggest another solution or at least capture your 
requirements for a future enhancement.

Thanks,
-Rick


Ronald Rudy wrote:
> Is there any way programmatically or otherwise to specify a java.util.Comparator type object to sort a specific column?  I realize this flies in the face of any SQL standard or convention, but it would be really helpful to me at the moment :).  And since Derby is Java-based, I was wondering if there might be something "non-standard" I could do to support it ....?
>
> My alternative is to create an additional column in my database that will give me the sorting I want (as it would be given by a custom Comparator class) using standard varchar sorting, but this isn't preferable.
>
> Thanks,
>    -Ron
>