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...
