> The only strange thing is that without the 3rd order by, the order is
> wrong. I didn't expect it because each select is created ordered. Is it
> expected that UNION mixes it all up? (using postgre 7.4.1)
That's because UNION removes duplicates, which it will probably doing
using a hash (EXPLAIN ANALYZE is your friend).Use UNION ALL because your WHERE condition allows no duplicates anyway.
UNION ALL preserves the order.So you can get the LIMIT out of the subqueries and put it around the
UNION ALL.