Okay seasoned DBAs... here's a question that just popped into my head. How would you do an order-by with relative ordering? Whereas "A must come before D" and "B must come before C" and "A must come before B" but aside from that everything's fine? So that ABCD is acceptably sorted, while ADBC also satisfies as sorted.

This would get important when doing an order-by on secondary and tertiary fields, instead of just one field. Or in non-english alphabets where two letters have no presidence before or after one another.

1/19/2006 5:12:42 PM

There are a few different ways to do this, and it really depends on how large the recordset is and how the user would be retrieving the data.

Probably the easiest way is to setup views for the common orders. MySQL 5 finally supports views.

1/19/2006 5:28:16 PM

using mysql you could split a field down by characters into seperate field then order by fielda,fieldb,fieldc, etc


probly a better way but thats about 30 seconds of thought

1/19/2006 5:28:29 PM

^^woah, it just now got views? what a POS

yeah, the idea just came to me at ass in the morning. it wouldn't specifically be able to handle something as loosly defined as the problem I gave, but in the case where our alphabet is "ABCD" and the order between BC and CB isn't important (as A>BCD, A>B>D, A>C>D, ABC>D)... my thought is to create a (deterministic) user function that transliterates "ABCD" to "1223" and then sorts based on the field. because the function is deterministic (and labeled so to Oracle), it can be used as a function-based index, and the result of the function could be cached, and the table sorted by using this index as a sort of materialized-view on a single column. I hope that makes sense. I like how clean it is...

mattc, i'm not sure I understand? like say I have a table with a 1 char field so i have 4 rows, {'A','B','C','D'} and I want to sort them. (sorry, now that i think about it that wasn't too clear above)

1/20/2006 8:13:59 AM

Give a real life example of something this would be needed. I'm having trouble seeing the point of this.

1/20/2006 8:41:13 AM

You can use CASE and (presumably) IF statements in order to generate an ORDER BY on the fly.

To test it out, I only used

ORDER BY CASE id
WHEN id <20
THEN id
ELSE parent

1/20/2006 10:29:34 AM

Arbitrarily we say é = e and ó = o.

We have a table of 4 rows:
cóté 1
coté 1
cote 3
cóté 2

and we want to say "sort it by the number" and we want to guarantee that our output order is

cóté 1
coté 1
cóté 2
cote 3

and could NEVER posibly be, because SQL sorts must be stable (so secondary and tertiary sorts can be done)

coté 1
cóté 1
cóté 2
cote 3

1/20/2006 10:30:19 AM

I also just tried

ORDER BY
CASE id
WHEN id <20
THEN id
ELSE parent,
IF (col1>0, col1, col2)

1/20/2006 10:37:11 AM

^clever

1/24/2006 12:10:44 AM

If you aren't using views... well, then... I'm sorry

1/24/2006 10:11:21 AM