Re: Ordering by IN

It might not be pretty but:

SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120
AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 )
AND utmpt IN ( 1 ) ORDER BY unit = 90072 desc, unit = 90005 desc, unit =
90074 desc;

It probably won't work very well if you need to specify more then about
10 IDs to order by.

I just discovered Postgres supports this syntax:

ORDER BY unit in ( 90072, 90005, 90074 ) desc

It seems to order the IDs in the reverse order they are listed in the IN
clause. I don't fully understand the behavior of the above case though,
it seems to do weird things with different queries. Give it a shot
though.

On Wed, 2004-08-25 at 18:18 +1200, Hadley Willan wrote:
> Hi,
> I was wondering if it's possible to order the result set by some
> of the set contained in an IN clause.
>
> For example.
> SELECT * FROM v_fol_unit_pub_utmpt WHERE folder_folder_object = 100120
> AND unit IN ( 90072, 90005, 90074, 90075 ) AND unit_pub_type IN ( 2 )
> AND utmpt IN ( 1 );
>
> Results in.
>
> folder_folder_object | unit | unit_type | unit_quantity |
> unit_pub_type | utmpt
> ----------------------+-------+-----------+---------------
> +---------------+-------
> 100120 | 90005 | 101 | 1 |
> 2 | 1
> 100120 | 90072 | 101 | 1 |
> 2 | 1
> 100120 | 90074 | 101 | 1 |
> 2 | 1
>
> When ideally I'd like to maintain the order as per ordered list of
> unit ids passed in as the parameters.
> Like so;
>
> folder_folder_object | unit | unit_type | unit_quantity |
> unit_pub_type | utmpt
> ----------------------+-------+-----------+---------------
> +---------------+-------
> 100120 | 90072 | 101 | 1 |
> 2 | 1
> 100120 | 90005 | 101 | 1 |
> 2 | 1
> 100120 | 90074 | 101 | 1 |
> 2 | 1
>
>
> Is this even possible?
>
> Thanks
--
Mike Benoit <ipso(at)snappymail(dot)ca>