1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Another unusual query

Discussion in 'MySQL' started by Weirfire, Apr 11, 2006.

  1. #1
    Is it possible to write a query in 1 go where you would order the results firstly where x = "this value" and then order by y?

    I have a table where the field "status" can be pending or complete or some other varieties and I want to order by firstly status=pending and then by deadline.
     
    Weirfire, Apr 11, 2006 IP
  2. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #2
    Yes it is possible :)













    
    select * from somewhere
    order by status, deadline
    
    Code (sql):
     
    dct, Apr 11, 2006 IP
    Weirfire likes this.
  3. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #3
    Thanks dct

    Thats all very well but I have statuses that come before and after "pending" so I cant use ORDER BY status or ORDER BY status DESC.


    Any other ideas?
     
    Weirfire, Apr 11, 2006 IP
  4. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #4
    Sure you can just add them like the columns you select:

    ... order by status desc, deadline asc

    or something like that. Desc = descending, asc= ascending.
     
    Perrow, Apr 11, 2006 IP
  5. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #5
    lol you replied before I got a chance to stick in my reply Perrow :)
     
    Weirfire, Apr 11, 2006 IP
  6. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #6
    For some reason I just typed too slow. When I started writing DCT hadn't answered yet.
     
    Perrow, Apr 11, 2006 IP
    Weirfire and dct like this.
  7. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #7
    lol

    I think I need some kind of query that says

    SELECT * FROM something
    ORDER BY status='pending', deadline DESC
     
    Weirfire, Apr 11, 2006 IP
  8. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #8
    If you have an id field there is a hacky way you could do it, say pending has a status_id of 10 do the following
    
    select * from something
    order by 
    abs(status_id - 10), deadline DESC
    
    Code (sql):
     
    dct, Apr 11, 2006 IP
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    Hmmm, I suppose if you're going to go to that trouble you might as well make an extra field called pending and set it to 1 if status is pending. That way you can just order the list by the pending field.

    Thanks for that little tip though dct. I've never seen that before.
     
    Weirfire, Apr 11, 2006 IP
  10. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #10
    I often add an order_by field for just that purpose, though sometimes it's good to hack :).
     
    dct, Apr 11, 2006 IP
  11. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #11
    I agree... hacking is fun!!! :D :eek: errrrr.....
     
    Weirfire, Apr 11, 2006 IP
  12. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #12
    This is why your status id's should always be prime numbers :eek:

    Searched the internet (called googling I'll teach you some day), found this:

    SELECT * FROM tickets
    ORDER BY FIELD(priority, 'High', 'Normal', 'Low', 'The Abyss');
    Code (sql):
    Should help you.

    Source: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
     
    Perrow, Apr 11, 2006 IP
  13. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #13
    Thanks Perrow not seen that before (from an MSSQL background).
    err what?
     
    dct, Apr 11, 2006 IP
  14. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #14
    So they get unique values when you do your little calculation, you don't want two status codes to wind up with the same value, now do you ;)

    Too much math in my head, I know :rolleyes:
     
    Perrow, Apr 11, 2006 IP
  15. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #15
    Just so you'll know, that post was my 1000th :D
     
    Perrow, Apr 11, 2006 IP
  16. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #16
    GENIUS!!!! That has to go down as the best 1000th post ever on DP! :)


    Thanks Perrow!
     
    Weirfire, Apr 11, 2006 IP
  17. Perrow

    Perrow Well-Known Member

    Messages:
    1,306
    Likes Received:
    78
    Best Answers:
    0
    Trophy Points:
    140
    #17
    That was actually number 999, 1000 was my explanation of the prime numbers rant, wich may not be the best ever 1000th post. But I'll settle for the best ever 999th post :D
     
    Perrow, Apr 11, 2006 IP
  18. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #18
    Shhhh! I was trying to get you some fame there buddy lol.
     
    Weirfire, Apr 11, 2006 IP