Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2005
    Posts
    79

    Unanswered: Query result order when using IN clause

    hi,

    The following query returns a result where the order of the rows is not in the desired order 10,5,9 but 5,9,10. I want to order the returned rows based on how they are ordered in the IN clause.

    SELECT * from test WHERE OrderID IN (10,5,9)

    OrderID is a primary key in table test

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    What you have is going to return anything with an order id in that group ordered by the order they are found. (That makes sense, right?.) If you want to explicitly set an order, you should use the ORDER BY clause, however I don't think you can set a "custom" order for an ORDER BY clause. What you may have to do is do a UNION ALL.

    Code:
    (SELECT * FROM test WHERE orderID = 10)
    UNION ALL
    (SELECT * FROM test WHERE orderID = 5)
    UNION ALL
    (SELECT * FROM test WHERE orderID = 9)
    If that won't work for your situation (perhaps you have many many orderIDs), the only other thing I can think of right now is to make a new table with your orderids and assign each an "order number" to specify the order in which they should appear. Then join on that table and sort by the order number.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jfulton
    however I don't think you can set a "custom" order for an ORDER BY clause.
    yes, you can
    Code:
    SELECT * from test WHERE OrderID IN (10,5,9)
    order by field(OrderID,10,5,9)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Ahhh, I stand corrected. There's another new trick up the sleeve. Maybe I should stick to the php forums .

    Nice to have you back though Rudy.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jfulton
    Nice to have you back though Rudy.
    thank you, i appreciate it, although i am not really back, i was just incredibly bored last night
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2005
    Posts
    79

    Smile

    Awesome
    thanks to both of you

Posting Permissions

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