MySQL order by dayname (custom formatted)

Question

The SQL I'm using on MySQL database is

SELECT
        CONCAT(YEAR(EVE_DATE),'-',MONTH(EVE_DATE),'-',DAYNAME(EVE_DATE))  AS WEEKDAY_DATE,
        SUM(EVE_OCCUR)
FROM
        TABLE
    WHERE
       EVE_DATE BETWEEN '2015-01-01' AND '2015-10-31'
    GROUP BY
            WEEKDAY_DATE 
    ORDER BY WEEKDAY_DATE

The output of the weekname it generates is in the format "YYYY-MON-DAY". Currently, the output is not ordered. I would like to order it as below

2015-01-Sun
2015-01-Mon
2015-01-Tue
2015-01-Wed
2015-01-Thu
2015-01-Fri
2015-01-Sat
2025-02-Sun
2015-02-Mon
2015-02-Tue
2015-02-Wed
2015-02-Thu
2015-02-Fri
2015-02-Sat
and so on

Could someone please help me?

Thanks for trying @lad2025. It gets the months in order but not Sun,Mon etc. SOme improvement over the original one. Thank you — usert4jju7, Nov 01 '15 at 07:59

Lukasz Szozda · Answer 1 · 2015-11-01T08:35:34.303

1

You can order by EVE_DATE:

SELECT
  CONCAT(YEAR(EVE_DATE),'-',MONTH(EVE_DATE),'-',DAYNAME(EVE_DATE))  AS WEEKDAY_DATE,
  SUM(EVE_OCCUR)
FROM TABLE
WHERE EVE_DATE BETWEEN '2015-01-01' AND '2015-10-31'
GROUP BY WEEKDAY_DATE 
ORDER BY EVE_DATE

SqlFiddleDemo

EDIT:

Example what I wanted to communicate in comment:

2015-01-01  - Thursday     -- 4th
2015-01-02  - Friday       -- 5th
2015-01-03  - Saturday     -- 6th
2015-01-04  - Sunday       -- 7th
2015-01-05  - Monay        -- should it be the first
2015-01-06  - Tuesday      -- 2nd
2015-01-07  - Wednesday    -- 3rd
2015-01-08  - Thurday      
2015-01-09  - Friday
2015-01-10  - Saturday
2015-01-11  - Sunday
2015-01-12  - Monday       -- 8th

When you sort by date you sort by actual date and not its textual representation.

Now you want to start from Monady but Monday does not exists in this year/month as starting point. Do you want to shuffle Monady? What you propose is nonsense for me.

edited Nov 01 '15 at 08:35

answered Nov 01 '15 at 07:58

Lukasz Szozda

162,964
23
234
275

@usert4jju7 See demo.I don't get your: `"It gets the months in order but not Sun,Mon "` They are ordered ascending – Lukasz Szozda Nov 01 '15 at 08:01
This definitely is better than the output I was getting earlier. What I'm missing now is the order Sun,Mon,Tue...Sat. How could I sort it in that order please? – usert4jju7 Nov 01 '15 at 08:04
@usert4jju7 I still don't get your point show your actual data. See that I order by date and use WEEK_DATE only as presentation. The sorting is based on dates not strings – Lukasz Szozda Nov 01 '15 at 08:06
The output I get now is 2015-01-Tue,2015-01-Thu,2015-01-Fri,2015-01-Sat,2015-01-Sun,2015-01-Mon. Then the same for month 02,03 & so on. This is great. The output is ordered by year & then followed by the month. I fully appreciate your help here. The last bit I'm missing is to additionally order it as 2015-01-Mon, 2015-01-Tue, 2015-01-Wed, 2015-01-Thu, 2015-01-Fri, 2015-01-Sat, 2015-01-Sun. Would you be able to help me with this last bit please? – usert4jju7 Nov 01 '15 at 08:13
Just like in here - http://stackoverflow.com/questions/1126961/order-by-day-of-week-in-mysql - ORDER BY (case DAYOFWEEK(dateField) WHEN 1 THEN 8 else DAYOFWEEK(dateField) END). Could we apply something like this to achieve what I'm after? – usert4jju7 Nov 01 '15 at 08:15
@usert4jju7 So it means that you don't have the data for `Monay` before Tuesday. If I understeand you correctly you want to get next Monday to move it before Tuesday, which is nonsese for me. Look at data like this `Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday, Monday` but the `Monday` belongs to **next week**. Also keep in mind that not every year/month starts with Monday – Lukasz Szozda Nov 01 '15 at 08:16
@lad2015-THe data could be in any order in the database table. Like in any other case, I've selected data & ordered it(with your query's help). If I understand the problem, it's the presence of alphabets(Mon,Tue etc) in "YYY-MM-Mon" which is causing the data to get sorted alphabetically. I've seen a few forums where similar problem is worked around just like in the example of stackoverflow I gave. See if you can help, else no problem. Thanks for your help so far. – usert4jju7 Nov 01 '15 at 08:23

score 1 · Answer 2 · answered Nov 01 '15 at 12:43

Why are you using that concat() statement when MySQL has date_format()?

SELECT DATE_FORMAT(eve_date, '%Y-%m-%a') as weekday_date,
       SUM(EVE_OCCUR)
FROM TABLE
WHERE eve_date >= '2015-01-01' AND eve_date < '2015-02-01'
GROUP BY WEEKDAY_DATE 
ORDER BY MIN(eve_date);

For the ORDER BY, I would recommend using an aggregation function, rather than ORDER BY eve_date. This is consistent with how standard SQL works.

I also changed the date comparisons to use >= and <. BETWEEN is a dangerous habit with dates, because it works differently when there is a time component. The above method works equally well for dates and date/times.

Shankar · Accepted Answer · 2015-11-01T08:28:33.460

0

You can use EVE_DATE column to sort the output in Query

SELECT
        CONCAT(YEAR(EVE_DATE),'-',MONTH(EVE_DATE),'-',DAYNAME(EVE_DATE))  AS WEEKDAY_DATE,
        SUM(EVE_OCCUR)
FROM
        TABLE
    WHERE
       EVE_DATE BETWEEN '2015-01-01' AND '2015-10-31'
    GROUP BY
            WEEKDAY_DATE 
    ORDER BY YEAR(EVE_DATE),MONTH(EVE_DATE) ,FIELD(DAYNAME(EVE_DATE), 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY', 'SUNDAY');

edited Nov 01 '15 at 08:28

answered Nov 01 '15 at 07:59

Shankar

846
8
24

Thank you Shankar. The output is ordered by year & month. I'm still missing Sun,Mon,Tue .. Sat. The current output orders the weekday name alphabetically – usert4jju7 Nov 01 '15 at 08:05
@usert4jju7 Could you give me more details , we are sorting by date, ideally it should yield the output you required , (2015-01-02 (Sun) 2015-01-03 (-Mon)), Output will be 2015-01-Sun, 2015-01-Mon – Shankar Nov 01 '15 at 08:10
The output I get now is 2015-01-Tue,2015-01-Thu,2015-01-Fri,2015-01-Sat,2015-01-Sun,2015-01-Mon. Then the same for month 02,03 & so on. This is great. The output is ordered by year & then followed by the month. The last bit I'm missing is to additionally order it as 2015-01-Mon, 2015-01-Tue, 2015-01-Wed, 2015-01-Thu, 2015-01-Fri, 2015-01-Sat, 2015-01-Sun. Would you be able to help me with this last bit please? Just like in here - http://stackoverflow.com/questions/1126961/order-by-day-of-week-in-mysql - ORDER BY (case DAYOFWEEK(dateField) WHEN 1 THEN 8 else DAYOFWEEK(dateField) END) – usert4jju7 Nov 01 '15 at 08:16
I got this , you are correct, Will update the solution shortly – Shankar Nov 01 '15 at 08:22
Perfect. I wholeheartedly appreciate your effort @Shankar. THank you very much. – usert4jju7 Nov 01 '15 at 08:39

MySQL order by dayname (custom formatted)

3 Answers3