Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Aug 2007
    Location
    Germany
    Posts
    22
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Question Ordering in queries by sequence of elements in "IN"

    Hello All,

    I need a query which delivers results in the order I have in the element sequence after "IN".

    Code:
    SELECT aid, cid, vid FROM table WHERE aid IN (5,7,3)
    I want to see the resuts of the query submitted in the same order, it means the first row should be with aid 5, the second with aid 7 and the third with 3.

    Without "ORDER BY" I get the results in sequence 3, 5, 7.

    Please help

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    Two ways to do this one proprietary, one standard sql.

    This one can be done in mysql only and isn't portable to other applications

    Code:
    ORDER BY FIELD(fieldname,5,7,3)
    and this one is portable

    Code:
    ORDER BY
    CASE
      WHEN fieldname = 5 then 0
      WHEN fieldname = 7 then 1
      WHEN fieldname = 3 then 2
    END

  • Users who have thanked guelphdad for this post:

    alex375 (11-01-2007)


  •  

    Posting Permissions

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