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;
And now my solution:
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
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...