Re: Ordering by IN

Mike Benoit <ipso(at)snappymail(dot)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