Thread: Order By Question

This seems like the answer must be pretty easy, but I can't think of it:

In the following statement:

select field1 from my_table where field2 in (3, 1, 2);

How can I modify this statement so that the record are returned in the
order of having field2 = 3, then field2 = 1, then field2 = 2.
As it stands, I am getting them returned in the order of the value of
field1.

Tristan Colson wrote:

> This seems like the answer must be pretty easy, but I can't think of it:
>
> In the following statement:
>
> select field1 from my_table where field2 in (3, 1, 2);
>
> How can I modify this statement so that the record are returned in the
> order of first those records having field2 = 3, then field2 = 1, then
> field2 = 2.
> As it stands, I am getting them returned in the order of the value of
> field1.

You can't =;^)

If you add field 2 to the select list and order by it, you can get them in
1,2,3 or 3,2,1 order.

Alternatively, you could add a case statement to your query to add a column
that would be used just to order the data.  My syntax may be foobar but it
would be something like:

select field1,   CASE       WHEN field2 = 3 THEN 1       WHEN field2=1 THEN 2        WHEN field2=2 THEN 3   AS
orderfield
from my_table
where field2 in (3,1,2)
order by orderfield;

- Ian