homepage Welcome to WebmasterWorld Guest from 54.227.215.140
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Complex ORDER BY statement
radiator251

5+ Year Member



 
Msg#: 4073307 posted 7:09 am on Feb 3, 2010 (gmt 0)

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

5+ Year Member



 
Msg#: 4073307 posted 7:17 am on Feb 3, 2010 (gmt 0)

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

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4073307 posted 4:22 pm on Feb 3, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved