order by in a non-numerical order

I'm trying to order the results of a query in a non alpha-numerical order. For example, if the possib开发者_JAVA百科le values of a column are '1', '2' and '3'. ORDER BY asc will display rows with '1' first, then rows with '2' and finally rows with '3'. ORDER BY desc will do the oppsite.

What if I want a different order, like 3, 1, 2 or 1, 3, 2 ? Is that possible ?

Thank you

You want to use a case statement in your order by clause.

So for your 3, 1, 2 example, it would look something like this:

ORDER BY
    CASE <yourField>
        WHEN 3 THEN 1
        WHEN 1 THEN 2
        WHEN 2 THEN 3
        ELSE 4 END ASC

It should be possible, however, you do not specify what order you are looking for. If you are just looking for a random order you can use ORDER BY NEWID().

you can add your own column named as example my_order and then order by my_order

You don't really have an ordering as much as a certain sequence. How would you even specify this? If we think of the collection of rows as an indexed array, then you could make a auxiliary index table:

position  refers_to
   1         3
   2         1
   3         2
   .         .
   .         .

Then you can join the tables ON(index_table.refers_to = my_table.that_column) ORDER BY index_table.position.