SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Ordering results using custom order.. eh...

    Yeah, bad topic title, sorry.

    I don't think this is possible, but it would be cool if it is somehow.
    Let say I have the following query:

    Code:
    SELECT some_field, some_field2 FROM some_table WHERE some_field IN(5,4,8,2);
    As I understand it, as soon as MySQL finds a row matching the IN statement, it'll be 'added' to the record set. Is there any way to have MySQL return the records ordered by the IN clause?

    Something like:

    Code:
    some_field    some_field2
    ----------    --------------
    5                blah
    4                woo
    8                eh?
    2                woo 2
    Instead of the following, which represents the regular sorting of the table's data:

    Code:
    some_field    some_field2
    ----------    --------------
    2                woo 2
    4                woo
    5                blah
    8                eh?
    I can rearrange the result set using PHP, but I'd love to know if MySQL offers something for this

    Thanks!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    there are two ways. this is cleaner and only works in mysql:
    Code:
    order by find_in_set(some_field,'5,4,8,2')
    this is about 30% faster and works in other DB's:
    Code:
    order by case some_field
               when 5 then 1
               when 4 then 2
               when 2 then 3
               when 8 then 4
             end

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    as long as you're timing the various solutions, longneck...
    Code:
    ... order by field(some_field,5,4,8,2)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    OK, those looks cool

    Rudy, the ORDER BY FIELD variation, does that work in other DBMS' too?

    Edit>>
    Just implemented this and it works a treat, woo!
    Thanks guys

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    FIELD() is a mysql proprietary function, but CASE is standard sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    But case is slower than FIELD()?

    Heh, at least I now know how to do this should I ever change the code for other DBMS'

  7. #7
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    find_in_set is the clear winner: as the number of elements go up, the proportinal increase in execution time is smaller. see the attached graph. methods with lower, flatter lines are better.

    first means the element i was searching for is the first in the list, while last indicates the last item in the list. examples:
    Code:
    this is a "first":
    select benchmark(10000000, find_in_set(4,'4,3,2,1'));
    
    this is a "last":
    select benchmark(10000000, find_in_set(1,'4,3,2,1'));
    Attached Images Attached Images

  8. #8
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    OK, I think I may go for the case, it's not too slow and it works in more DBMS'.

    Thanks for the research!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    fascinating

    thanks, longneck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You always go the extra mile longneck, thanks for the extra analysis.


Bookmarks

Posting Permissions

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