Thread: Ordering by IN

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

On Aug 25, 2004, at 2:18 AM, 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.
>

I had to do something like this recently.  Ended up with a pl/pgsql
function, looked a lot like this:

create or replace function array_find (int8, int8[]) returns int4 as
     'declare
         data alias for $1;
         arr alias for $2;
         cnt int4;
      begin
         cnt := 1;
         while arr[cnt] is not null loop
             if data = arr[cnt] then
                 return cnt;
             end if;
             cnt := cnt + 1;
         end loop;

         return null;
      end;'
language 'plpgsql';

Then, using your original query:

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 array_find(unit, '{90072, 90005, 90074, 90075}');

Notice that the values are repeated in the ORDER BY clause, in the form
of an array.  VeryImportant.  Also note that I'm assuming the datatype
of "unit" is an int8... you'll want to adjust the arguments of the
function appropriately for the actual datatype.

With my luck, somebody will respond with a "but postgres already has an
array_find-like function"... but if it does, I couldn't find it.

eric

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@snappymail.ca>

Mike Benoit <ipso@snappymail.ca> writes:

> 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.

That's just sorting by the boolean value of whether unit is in the set or not.
It's not doing what you want.

You could do something like

 SELECT *
   FROM a JOIN (           select 90072 as unit
                 union all select 90005
                 union all select 90074) as x using (unit)

But even that is NOT going to be guaranteed to work. If it happens to choose a
nested loop from the union against a then I think it would result in the right
order. But if it decides to use a hash join or merge join then it's going to
result in other orderings.

You would have to make that more elaborate and cumbersome with

 SELECT *
   FROM a JOIN (          select 90072 as unit, 1 as pos
                union all select 90005,2
                union all select 90074,3
               ) as x using (unit)
  ORDER BY pos


If you load the very useful contrib/intarray module you could use the clean
nice notation:

ORDER BY idx(array[90072,90005,90074], unit)

--
greg