Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2004
    Posts
    27

    Unanswered: order by being ignored?

    ok i'm at a loss on why this is happening. I've got a query (see below) that i'm running a simple enough query nothing fancy. But its not ordering per the query order by.

    Code:
    select *,
      CONCAT(firstname,' ',lastname) as name, 
      date_format(in_dt,'%c/%e/%Y') as dt_fmt 
    from 
      Poeple_of_interest 
    where 
      People_of_interest.id in (".$peeps.") 
    order by 
      time_to_call='Morning' DESC, 
      time_to_call='Afternoon' DESC, 
      time_to_call='Evening' DESC, 
      time_to_call='Night' DESC, 
      time_to_call='Unknown' DESC, 
      time_to_call='' DESC, 
      time_to_call=NULL DESC
    $peeps is a php generated list of folks ('they','are','generated','in','this',fashion') but the problem is that if the record has NULL in the "time_to_call" field then it is automatically sent to the top of the list rather then the bottom as the order by is stating. However the rest of the order by is working fine.

    MySQL ver. is 3.23.58 (yes i'd love to update it but its not my call, and i've been around that tree)

    Any help would be greatly appreciated.
    Last edited by wardly; 01-27-05 at 10:13.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    order by
    case when time_to_call is null then 1 else 0 end ,
    time_to_call='Morning' DESC,
    time_to_call='Afternoon' DESC,
    time_to_call='Evening' DESC,
    time_to_call='Night' DESC,
    time_to_call='Unknown' DESC,
    time_to_call='' DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    27
    That generated an error

    Not sure how "case" works but will be looking that one up

  4. #4
    Join Date
    Dec 2004
    Posts
    27
    Is there anyway to determine if MySQL ver 3.23.58 supports case? (really missing PHP's manual about now when it tells you on ever function what vers support it)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    other than browsing forty of fifty pages called "Changes in Release 3.23.xx" on the mysql.com site, the easiest way that i can think of to determine if your version supports CASE is to write a query that uses it and see if it gets a syntax error

    you could also try the IF function instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2004
    Posts
    27
    Ok i moved the case statement up to the select portion of the query and it didn't error out but it didn't order it either. Although i changed it and not sure if i got it correct for what we are trying to do.

    Code:
    select *,
    CONCAT(firstname,' ',lastname) as name, 
    date_format(in_dt,'%c/%e/%Y') as dt_fmt, 
    case when time_to_call=NULL then time_to_call='Unknown' end
    from 
    People_of_interest
    where 
    People_of_interest.id in (".$peeps.") 
    order by 
    time_to_call='Morning' DESC, 
    time_to_call='Afternoon' DESC, 
    time_to_call='Evening' DESC, 
    time_to_call='Night' DESC, 
    time_to_call='Unknown' DESC, 
    time_to_call='' DESC
    Not sure about the IF statement, i don't see any examples of it being tied to a field. Could you provide an example of it tied to a field in this fasion?
    Last edited by wardly; 01-27-05 at 11:18.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you cannot say foo=NULL, in a CASE expression or elsewhere

    you have to say foo IS NULL

    also, when you put THEN time_to_call='Unknown' , you get a 0 or 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2004
    Posts
    27
    out of the mysql manual

    CASE was added in MySQL 3.23.3.

  9. #9
    Join Date
    Dec 2004
    Posts
    27
    So you get a true or false return, but how do you order that? (i'm getting confused on how we are ordering that if we are only getting true or fasle returned)

    i made the changes but there is no difference in the ordering but i don't get the an error either.
    Last edited by wardly; 01-27-05 at 11:30.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you are getting confused now, then how come you put all those true/false expressions into your original ORDER BY, and especially DESC??

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2004
    Posts
    27
    There isn't true/false expressions in the order by (is there lol) i was simply trying to tell it the order i wanted it to be sorted. Thought thats what i was doing lol

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there sure are

    what do you think time_to_call='Morning' is?

    it evaluates true/false

    you sort all your rows based on a number of factors, with whether time_to_call='Morning' or not being the first one -- in reverse order!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2004
    Posts
    27
    rofl, i didn't know that. I copied that out of the MySQL manual ( http://dev.mysql.com/doc/mysql/en/sorting-rows.html ) towards the bottom there is a comment from a user.

    Ok so if i removed all the DESC it should work then?

  14. #14
    Join Date
    Dec 2004
    Posts
    27
    ok i made some more changes to counter my stupidity on the order by :P

    Code:
    select *,
    CONCAT(firstname,' ',lastname) as name, 
    date_format(in_dt,'%c/%e/%Y') as dt_fmt, 
    case when best_time_to_call is NULL then 1 else 0 end
    from 
    People_of_interest
    where 
    People_of_interest.id in (".$peeps.") 
    order by 
    time_to_call=NULL,
    time_to_call='',
    time_to_call='Unknown', 
    time_to_call='Night', 
    time_to_call='Evening', 
    time_to_call='Afternoon', 
    time_to_call='Morning'
    But we are in the same place that we where, the NULL values are at the top
    Last edited by wardly; 01-27-05 at 11:47.

  15. #15
    Join Date
    Dec 2004
    Posts
    27
    All i'm looking for is it to sort them in this order,

    morning
    afternoon
    evening
    night
    unknown
    ''
    NULL

    it really don't matter how we get them in that order as long as they are in that order. I thought thats what i was doing. I reversed the order by and removed the DESC and got the same result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •