Skip site navigation (1) Skip section navigation (2)

Re: Ordering by IN

From: Mike Benoit <ipso(at)snappymail(dot)ca>
To: Hadley Willan <hadley(dot)willan(at)deeperdesign(dot)co(dot)nz>
Cc: PGSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Ordering by IN
Date: 2004-08-25 16:01:10
Message-ID: 1093449670.7168.10.camel@ipso.snappymail.ca (view raw or flat)
Thread:
Lists: pgsql-general
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>


In response to

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2004-08-25 16:09:58
Subject: Re: copy a database
Previous:From: Marc G. FournierDate: 2004-08-25 15:39:53
Subject: Re: Unsupported 3rd-party solutions (Was: Few questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group