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 <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
