|Complex ORDER BY statement|
I have a table of events that repeat on certain days each week, and the way I store that information is in one column called "repeat". If the event happens on mondays, wednesdays, and fridays, the "repeat" column would read "monwedfri."
This has worked fine for my queries so far, where I just use WHERE `repeat` LIKE '%mon%' to check if it happens on Monday.
Now I want to do something more complex - I want to get all the events, and order them by the days on which they repeat. So, events that happen on sundays would be first, followed by mondays, etc.
I tried something like this:
ORDER BY `repeat1` LIKE '%sun% DESC, `repeat1` LIKE '%mon% DESC, `repeat1` LIKE '%tue% DESC, `repeat1` LIKE '%wed% DESC, `repeat1` LIKE '%thu% DESC, `repeat1` LIKE '%fri% DESC, `repeat1` LIKE '%sat% DESC
...but I get a MySQL syntax error, so obviously this isn't how you do it. I read somewhere about using "CASE" in the order by statement, but I can't find anywhere documenting exactly how to do that.
I think I've figured it out. I've gotten them to display in order of whether there's a "sun", "mon", "tue", etc, but I want them to be ordered by title within those days.
Here's what I did:
BY case when `repeat1` LIKE '%sun%' then 1 else 0 end DESC, case when `repeat1` LIKE '%mon%' then 1 else 0 end DESC, case when `repeat1` LIKE '%tue%' then 1 else 0 end DESC, case when `repeat1` LIKE '%wed%' then 1 else 0 end DESC, case when `repeat1` LIKE '%thu%' then 1 else 0 end DESC, case when `repeat1` LIKE '%fri%' then 1 else 0 end DESC, case when `repeat1` LIKE '%sat%' then 1 else 0 end DESC, `title` ASC"
For example, the first dozen or so listed are:
Late Night Specials-sunmontuewedthufrisat
Angie Mattson Music-sun
Groovin on a Sunday-sun
Border Grill Happy Hour-montuewedthufri
As you can see, they are ordered by title within the ones with the exact same `repeat1` column. When it is different, however, even if it still has the day that I'm searching for, the ORDER BY `title` restarts.
However, when I put the `title` ASC part after, say, the '%sun%' case, all of the events on sunday are ordered by title, just how I want them. I tried putting `title` ASC after each case, but that didn't do the trick.
So, looks like I'm doing the case-when-etc thing wrong...how can I fix this? It would be really great if I could do this all in one query without having to resort to 7 different ones.
I think it would be better to use the CASE in the SELECT part of the statement and do something like (not exactly sure of the exact syntax, but this should get you in the right direction):
SELECT title, repeat1, CASE
repeat1 LIKE '%sun%' THEN 1
repeat1 LIKE '%mon%' THEN 2
ORDER BY SortOrder, Title