Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: Select ordering...

    I have a query which can be big or small. Basically what it does is something like this...
    select * from table where a = 3 or a = 2 or a = 4

    The problem I'm having is when I'm fetching the array it doesn't return in the same order as I searched for it. So in this example I want the results returned in 3,2,4 not 2,3,4 like it does. Is there any way to fix this?
    vBulletin Hacking God - Portfolio - Where people talk about FireFox.

  2. #2
    Join Date
    Dec 2004
    SQL Results have no "defined order" as long as you dont' put an "order by" clause in the select, which will not help in your case if I understand it correctly.

    An "unsure" workaround may be the following

    select * from table where a=3
    union all
    select * from table where a=2
    union all
    select * from table where a=4.

    (depens on the RDBMS+Version which order your get).

    The real good (?) solution would be:

    create table "table2" (ordering int, searcharg int)
    insert into table2 values (1, 3)
    insert into table2 values (2, 2)
    insert into table2 values (3, 4)

    select table.* from table , table2
    where table.a = table2.searcharg
    and table.a in (3,2,4)
    order by table2.ordering

    (I've put als the "or"-predicates in an "in" -predicate, which normally performs better).

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    mysql has a really neat function you can use for this custom ordering

    SELECT foo, bar
    FROM yourtable
    WHERE a IN (3,2,4)
    ORDER BY FIELD(a,3,2,4) | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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