Stripe the order of a PostgreSQL result set
Update
WITH x AS ( SELECT * , row_number() OVER (ORDER BY number) rn_up , row_number() OVER (ORDER BY number DESC) rn_down FROM test )SELECT id, numberFROM xORDER BY LEAST(rn_up, rn_down), number;Or:
...ORDER BY LEAST(rn_up, rn_down), number DESC;to start with the bigger number.
I had two CTE at first, but one is enough - simpler and faster.
Or like this (similar to the already given answer but slightly shorter :)
WITH x AS ( SELECT *, row_number() OVER (ORDER BY number) rn, count(*) over () as c FROM test )SELECT id, numberFROM x ORDER BY ABS((c + 1.5) / 2 - rn) DESC;If the reverse order is needed then it should be
ORDER BY ABS((c + 0.5) / 2 - rn) DESC;