Complex ORDER BY statement

[radiator251]

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.

Any ideas?

Thanks.

[radiator251]

UPDATE:

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:

ORDER
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:

Happy Hour-sunmontuewedthufrisat
Happy Hour-sunmontuewedthufrisat
Late Night Specials-sunmontuewedthufrisat
Noches Calientes-sunmontuewedthufrisat
Happy Hour-sunmontuewed
Saloon Special-sunmonwedthufrisat
Happy Hour-sunmon
Angie Mattson Music-sun
Beefsteak Sunday-1sun
Beefsteak Sunday-1sun
Groovin on a Sunday-sun
Tapas Sundays-sun
Afternoon Delight-montuewedthufri
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.

Thanks.

[LifeinAsia]

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
...
AS SortOrder
ORDER BY SortOrder, Title