SQL Query Sorting Days.

Well, you could try this :

select DAYS
from yourtable
ORDER by to_char(to_date(DAYS,'DY'),'D');

Note that to_char(to_date(DAYS,'DY'),'D') will return you 1 where DAYS has a value MON, 2 for TUE ... and so on ...

Hope this helps.

Looks like you might get a trouble with my query. I will have to find out the solution for that, but this is the problem that you may encounter :

ERROR at line 1:
ORA-01835: day of week conflicts with Julian date

So, you may stick to cjjclifford's solution!

I started to play around with the same type of stuff, and saw the "Julian" error too... I'd like to see if it can be done without the verbose DECODE() I suggested...

I agree with  cjjclifford. Decode/case is the only solution.

  1  select A
  2   from mytest1
  3  order by case when a='MON' then 1
  4  when a='TUE' then 2
  5  when a='WED' then 3
  6  when a='THU' then 4
  7  when a='FRI' then 5
  8  when a='SAT' then 6
  9  when a='SUN' then 7
 10* end
SQL> /

A
===
MON
TUE
WED
THU
FRI
SAT
SUN

you might want to add an "else NULL" if you are using the CASE approach, to guard against unexpected data in the table, similar to the end NULL in my DECODE().

Actually, you should probably set a check constraint on the table to ensure the day text is only one of ( 'MON', 'TUE', etc ), guarantee the values at insert time!

Alternatively you could create a small function and use it in your query.

  1  create or replace function DOW(istr varchar2) return number
  2  is
  3  begin
  4  return case when istr = 'MON' THEN 1
  5              when istr = 'TUE' THEN 2
  6              when istr = 'WED' THEN 3
  7              when istr = 'THU' THEN 4
  8              when istr = 'FRI' THEN 5
  9              when istr = 'SAT' THEN 6
 10              when istr = 'SUN' THEN 7
 11              else null end;
 12* end;
SQL> /

Function created.

SQL> select * from tab_chr1;

FLD1
------------------------
MON
TUE
WED
FRI
SUN
SAT
THU
TUE

8 rows selected.

SQL> select * from tab_chr1 order by DOW(fld1);

FLD1
------------------------
MON
TUE
TUE
WED
THU
FRI
SAT
SUN

8 rows selected.

SQL>

Thank you both for your comments and they work when I am using SQL Plus, however, when I use the same SQL command on Oracle Report builder for some reason it does not seem to work. Any suggestions for this?

Thanks

There should be no reason for a difference - perhaps it is a problem with the quoting of the 'MON', etc, strings? (I haven't user Report Builder, so I'm guessing...)

What does it mean " ... it does not seem to work" ? Are there errors there ?

I mean that there are no errors on the Report Builder it just does not sort it to Mon, Tues etc, the output remains as Fri, Tue, Thu etc. I think it may be something to do with the fact that I am using the Days as Group in the Report builder.

Grouping and ordering are seperate.

Are you sure your data in the DAYS column is clean (check with "SELECT DISTINCT days FROM tablename")

Do you have a case-sensitivity issue? use UPPER( days ) in the DECODE() : DECODE( UPPER( days ), 'MON', 1, 'TUE', 2, etc ...

If you are having problems with using the decode/case in report builder, create a simple view of your table to add the day index:

CREATE OR REPLACE VIEW table_with_dayid AS
SELECT *, DECODE( UPPER( days ), 'MON', 1, 'TUE', 2, 'WED', 3, 'THUR', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, NULL ) day_id
FROM tablename;

This view should have all the same columns, and one additional one "DAY_ID".
Now use this view in your report, rather than the original table, and just ORDER BY day_id.

I think that you are right - see this topic in the on-line help "Query modifications".

FYI,

I have now resolved this problem by using the faithful DECODE function. I added a new field with DECODE( days, 'MON', 1, 'TUE', 2, 'WED', 3, 'THU', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, null ) as "NumDay", in addition to the field days. I ran the report on ORacle Report Builder using NUMDAY as the group level and then hid the Num Day field. Thank you all for your help you got me on the right track