Re: Ordering by IN

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