Oracle 10g-Express Query
I want to perform a special query on a table in my oracle database.
I want the result to be sorted according to an enum that I have.
the enum goes as follows:
p开发者_开发知识库rivate enum days
{
Saturday = 1,
Sunday,
Monday,
Tuesday,
Wednesday,
Thursday,
}
I want the result to be sorted according to this enum.
So, your ENUM is a front-end thing which the database cannot see. All you can do is duplicate its logic in the query. In Oracle we can use functions like CASE() to translate a column value into a different value for sorting.
SQL> select d2
2 , to_char(d2, 'DY')
3 from t34
4 ORDER BY CASE to_char(d2, 'DY')
5 WHEN 'SAT' THEN 1
6 WHEN 'SUN' THEN 2
7 WHEN 'MON' THEN 3
8 WHEN 'TUE' THEN 4
9 WHEN 'WED' THEN 5
10 WHEN 'THU' THEN 6
11 WHEN 'FRI' THEN 7
12 ELSE 100 END
13 /
D2 TO_
--------- ---
25-JUL-10 SUN
24-AUG-10 TUE
13-JUL-10 TUE
26-MAY-10 WED
15-APR-10 THU
25-JUN-10 FRI
6 rows selected.
SQL>
The exact values of the date abbreviations will depend on the setting of NLS_DATE_LANGUAGE parameter. Find out more.
Oracle date formatting also allows us to convert a DATE into a number for day of the week. This is a cultural thing: some societies have Monday as the first day of the week, others Sunday or Saturday. So TO_CHAR(some_date, 'D') will return 1 for a date which is a Monday in Europe but 2 in USA. This is controlled by the NLS_TERRITORY settings. Find out more.
If your Territory is set so that TO_CHAR(date_col, 'D') returns 1 for a date which is a Saturday the ORDER BY clause is much simpler:
SQL> select * from nls_session_parameters
2 where parameter = 'NLS_TERRITORY'
3 /
PARAMETER VALUE
------------------------------ -----------------
NLS_TERRITORY UNITED KINGDOM
SQL> select d2
2 , to_char(d2, 'DY')
3 , to_char(d2, 'D')
4 from t34
5 ORDER BY to_char(d2, 'D')
6 /
D2 TO_ T
--------- --- -
13-JUL-10 TUE 2
24-AUG-10 TUE 2
26-MAY-10 WED 3
15-APR-10 THU 4
25-JUN-10 FRI 5
25-JUL-10 SUN 7
6 rows selected.
SQL>
If I change the NLS_TERRITORY the result set order changes accordingly:
SQL> alter session set nls_territory='MOROCCO'
2 /
Session altered.
SQL> select d2
2 , to_char(d2, 'DY')
3 , to_char(d2, 'D')
4 from t34
5 ORDER BY to_char(d2, 'D')
6 /
D2 TO_ T
-------- --- -
25-07-10 SUN 2
24-08-10 TUE 4
13-07-10 TUE 4
26-05-10 WED 5
15-04-10 THU 6
25-06-10 FRI 7
6 rows selected.
SQL>
order by
case to_char(datecol, 'D')
when 1 then 2
when 2 then 3
...
when 7 then 1
end
I've upvoted APCs solution, this is the best / right way to do it.
However, I wanted to add an answer that applies to the general case of ordering by an arbitrary set of data.
(In this specific case, the ordering is not arbitrary - it is something we can write as a simple SQL function - so should).
The key thing here is that an enum is a SET. A Table is also a set, and so is a View - so simply convert your enum into a table or view, then in your SQL queries you can join to / order by this table.
This is especially useful if you need to re-use it in many queries.
For a small fixed set like this I would simply hard-code it into a view - for larger sets an indexed table would be better.
Being honest - if it was real one-off - if I knew that the ordering would only ever be used in a single query - I would use the CASE approach in the main answer.
精彩评论