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
