I have the following problem which could (I am hoping) have a standard solution.
I have a java application that interacts with the database and builds dynamically SQL strings.
So far I guess usual stuff.
Occusionally I need to have a sorting on the data and I use ORDER By. So far clear I guess.
Problem: I sometimes need to sort on a column that does not have the actual data but a short string that is a key to the actual data.
I mean:
SELECT FROM MYTable WHERE MyTable.col1 = 'A' ORDER BY MyTable.col2 ASC
And col2 has values: AB, BY, CY which return sorted but are useless to me as these are keys to the actual values from a properties file and so the end result is not sorted.
What is the best way to solve this in a manner consistent with the way it currently works?
解决方案
If the data is not in the DB, then your choice is to either sort it in Java, or first insert the data from the property file into a temporary table and then join to that table and do the ORDER BY in SQL. If this is an operation that happens frequently, you might consider putting the properties into a permanent table.