• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1199
  • Last Modified:

SQL Query Sorting Days.

I have a column titled 'DAYS' and the fields are MON to SUN, these are of character format and I want to order by these. How can I get the SQL query to order by the days and leave it either as MON or Monday as opposed to formatting it to a date.
0
leigh_richardson
Asked:
leigh_richardson
  • 5
  • 3
  • 3
  • +2
1 Solution
 
cjjcliffordCommented:
you could use a DECODE() to convert from MON, TUE, etc to a number, and use that for ordering:

-- Null at the end in case there are other values in table...
select days from testtable ORDER BY DECODE( days, 'MON', 1, 'TUE', 2, 'WED', 3, 'THUR', 4, 'FRI', 5, 'SAT', 6, 'SUN', 7, null );

or you could look into using TO_DATE() to convert the day to a date somehow.... (formatter 'day' will provide the day for a given date, so you might be able to use that in reverse, to get a numeric value for a given day....)

select to_char( sysdate, 'day' ) from dual;
0
 
pratikroyCommented:
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.

0
 
pratikroyCommented:
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!
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
cjjcliffordCommented:
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...
0
 
riazpkCommented:
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
0
 
cjjcliffordCommented:
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!
0
 
pratikroyCommented:
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>
0
 
leigh_richardsonAuthor Commented:
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
0
 
cjjcliffordCommented:
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...)
0
 
Helena Markováprogrammer-analystCommented:
What does it mean " ... it does not seem to work" ? Are there errors there ?
0
 
leigh_richardsonAuthor Commented:
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.
0
 
cjjcliffordCommented:
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.
0
 
Helena Markováprogrammer-analystCommented:
I think that you are right - see this topic in the on-line help "Query modifications".
0
 
leigh_richardsonAuthor Commented:
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now