CODEWITHSUNDEEP
mysqlsqlsql-order-bydayofweek
Bogdan Gusiev
Bogdan Gusiev

Reputation: 8305

Order by day_of_week in MySQL

How can I order the mysql result by varchar column that contains day of week name?

Note that MONDAY should goes first, not SUNDAY.

Upvotes: 18

Views: 26877

Answers (12)

dabadoob
dabadoob

Reputation: 1

I saw that ...WHEN 1 THEN 7... was posted but it should be WHEN 1 THEN 8. So...

ORDER BY ( CASE DATEPART(DW, yourdatefield) WHEN 1 THEN 8 ELSE DATEPART(DW, yourdatefield) END )

Otherwise Sunday may come before Saturday because both Sunday and Saturday would equal 7. By setting Sunday to 8, it ensures it comes after Saturday.

Upvotes: 0

M E S A B O
M E S A B O

Reputation: 953

In my case, since the days can be registered in several languages, to get the correct order I do like this according to Glen Solsberry:

....
....
    ORDER BY 
    FIELD(<fieldname>, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY'),
    FIELD(<fieldname>, 'LUNDI', 'MARDI', 'MERCREDI', 'JEUDI', 'VENDREDI', 'SAMEDI', 'DIMANCHE'),
    FIELD(<fieldname>, 'LUNES', 'MARTES', 'MIERCOLES', 'JUEVES', 'VIERNES', 'SABADO', 'DOMINGO'),
    FIELD(<fieldname>, 'MONTAGE', 'DIENSTAG', 'MITTWOCH', 'DENNERSTAG',  'FREITAG', 'SAMSTAG', 'SONNTAG')
    ;

Do not forget that, <fieldname> is the name of the date column in question in your case.

Upvotes: 0

Lamri Djamal
Lamri Djamal

Reputation: 291

Found another way, your can reverse order bye week

ORDER BY date_format(date_name, '%w') DESC;

Upvotes: 1

Jensoo
Jensoo

Reputation: 5

Found another way that works for me:

SELECT LAST_NAME, HIRE_DATE, TO_CHAR(HIRE_DATE, 'fmDAY') as 'Day' FROM EMPLOYEES
ORDER BY TO_CHAR(HIRE_DATE, 'd');

Hope it helps

Upvotes: 0

Ali G
Ali G

Reputation: 1

If you try this, it should work:

SELECT ename, TO_CHAR(hiredate, 'fmDay') as "Day" 
FROM my_table
ORDER BY MOD(TO_CHAR(hiredate, 'D') + 5, 7)

Upvotes: -1

Joseph Manganelli
Joseph Manganelli

Reputation: 71

Why not this?

ORDER BY (
    CASE DAYOFWEEK(dateField)
    WHEN 1 THEN 7 ELSE DAYOFWEEK(dateField)
    END
)

I believe this orders Monday to Sunday...

Upvotes: 7

John Hawkins
John Hawkins

Reputation: 325

I realise that this is an old thread, but as it comes to the top of google for certain search times I will use it to share my approach.

I wanted the same result as the original question, but in addition I wanted the ordering of the results starting from the current day of the week and then progressing through the rest of the days.

I created a separate table, in which the days were listed over a fortnight, so that no matter which day you started from you could run through a sequence of 7 days.

CREATE TABLE IF NOT EXISTS `Weekdays` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

INSERT INTO `Weekdays` (`id`, `name`) VALUES
(1, 'Monday'),
(2, 'Tuesday'),
(3, 'Wednesday'),
(4, 'Thursday'),
(5, 'Friday'),
(6, 'Saturday'),
(7, 'Sunday'),
(8, 'Monday'),
(9, 'Tuesday'),
(10, 'Wednesday'),
(11, 'Thursday'),
(12, 'Friday'),
(13, 'Saturday'),
(14, 'Sunday');

I then ran the query with a variable that determined the start point in sequence and used a join to get the order number for the days. For example to start the listing at Wednesday, I do the following:

SELECT @startnum := MIN(id) FROM Weekdays WHERE name='Wednesday';
SELECT * FROM Events INNER JOIN ( SELECT id as weekdaynum, name as dayname FROM Weekdays WHERE id>(@startnum-1) AND id<(@startnum+7) ) AS s2 ON s2.dayname=Events.day ORDER BY weekdaynum;

I hope this helps someone who stumbles onto this post.

Upvotes: 1

shantanuo
shantanuo

Reputation: 32296

... ORDER BY date_format(order_date, '%w') = 0, date_format(order_date, '%w') ;

Upvotes: 2

niteria
niteria

Reputation: 1395

This looks messy but still works and seems more generic:

select day, 
case day
  when 'monday' then 1
  when 'tuesday' then 2
  when 'wednesday' then 3
  when 'thursday' then 4
  when 'friday' then 5
  when 'saturday' then 6
  when 'sunday' then 7
end as day_nr from test order by day_nr;

Using if is even more generic and messier:

select id, day, 
if(day = 'monday',1,
  if(day = 'tuesday',2,
    if(day = 'wednesday',3,
      if(day = 'thursday',4,
        if(day = 'friday',5,
          if(day = 'saturday',6,7)
        )
      )
    )
  )
) as day_nr from test order by day_nr;

You can also hide the details of conversion from name to int in stored procedure.

Upvotes: 1

Dave
Dave

Reputation: 4414

Another way would be to create another table with those days and an int to order them by, join that table when searching, and order by it. Of course, joining on a varchar is not recommended.

Table DaysOfWeek
id       | day
--------------------
1        | Monday
2        | Tuesday
3        | Wednesday
4        | Thursday
5        | Friday
6        | Saturday

SELECT * FROM WhateverTable LEFT JOIN DaysOFWeek on DaysOFWeek.day = WhateverTable.dayColumn ORDER BY DaysOfWeek.id

(Apologies if that's not correct; I've been stuck with SQL server recently)

Again, this is NOT recommended, but if you cannot alter the data you've already got... This will also work if there are non-standard values in the dayColumn field.

Upvotes: 0

Glen Solsberry
Glen Solsberry

Reputation: 12320

Either redesign the column as suggested by Williham Totland, or do some string parsing to get a date representation.

If the column only contains the day of week, then you could do this:

ORDER BY FIELD(<fieldname>, 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY');

Upvotes: 44

Williham Totland
Williham Totland

Reputation: 28999

I'm thinking that short of redesigning the column to use an enum instead, there's not a lot to be done for it, apart from sorting the results after you've gotten them out.

Edit: A dirty hack is of course to add another table with id:weekday pairs and using joins or select in selects to fake an enum.

Upvotes: 4

Related Questions