alternating sort order

I know, i'm lazy, i haven't written new blog-entries for a long, long time.


Someone ask in the german Postgresql-Forum: how can i sort in alternating order?

Okay, after some hours i found this solution:

Image, you have this table:

test=*# select * from foo;
i                      
----                    
1                     
2                     
3                     
4                     
5
6
7
8
9
10
(10 rows)

And now my solution:

test=# with
f1 as (
  select i, row_number() over (order by i)::float as nr from foo limit (select count(*) from foo)/2),
f2 as (
  select i, row_number() over()::float+0.5 from (
    select i from foo offset (select count(*) from foo)/2)x
)
select * from f1  union select * from f2  order by nr;
i  | nr
----+-----
1 |   1
6 | 1.5
2 |   2
7 | 2.5
3 |   3
8 | 3.5
4 |   4
9 | 4.5
5 |   5
10 | 5.5
(10 rows)

The first subquery returns the first half of the table with a new numbering column, starting
with 1 increment by 1.
The second returns the other half. Because the row_number() - function would start with the
offset, i have moved the row_number() function out of this subquery and created a other subquery around, to start my row_number with 1. After that, i added 0.5 to this (of course, i have to cast the int to a float to do that)

Yeah, now i'm able to sort in alternating order.

And yes, sorry about my bad english...