about me and my world

Montag, 12. Oktober 2009

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

Labels