From: Ian Harding <iharding@pakrat.com>
Subject: Re: Order By Question
Date: Mon, 22 Jan 2001 17:20:39 -0800
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 35
Message-ID: <3A6CDC67.4A79076E@pakrat.com>
References: <3A6CD7E4.8BE6DDC3@pacbell.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: pgsql-sql@postgresql.org

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