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