Stripe the order of a PostgreSQL result set

postgresql, sorting, sql, sql-order-by

Solution

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, number
FROM   x
ORDER  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.

Problem

Let's say I have the following table: ``` create temp table test (id serial, number integer); insert into test (number) values (5), (4), (3), (2), (1), (0); ``` If I sort by number descending, I get: ``` select * from test order by number desc; id | number ---+-------- 1 | 5 2 | 4 3 | 3 4 | 2 5 | 1 6 | 0 ``` If I sort by number ascending, I get: ``` select * from test order by number asc; 6 | 0 5 | 1 4 | 2 3 | 3 2 | 4 1 | 5 ``` How do I stripe the order so that it alternates between ascending and descending per row? for example: ``` 6 | 0 or 1 | 5 1 | 5 6 | 0 5 | 1 2 | 4 2 | 4 5 | 1 4 | 2 3 | 3 3 | 3 4 | 2 ```

Original source