I've got this table (PostgreSQL 9.3
):
x | y
- | -
1 | 1
2 | 2
2 | 3
1 | 4
Now I try to get three partitions out of it: Every time the value x is changing (by ordering y), a new dense_rank
value should be given. Now I tried the following:
SELECT x, y, dense_rank() over (partition by x order by y)
FROM table
But with the partition over x the ordering is not working the way I expect. The result is
x y dense_rank
- - ----------
1 1 1
1 4 2
2 2 1
2 3 2
instead of the expected:
x y dense_rank
- - ----------
1 1 1
2 2 2
2 3 2
1 4 3
Now I am not sure why the window is not ordered by y.
In the second step I need this rank for a grouping (GROUP BY dense_rank, x). So in the end I need the following result:
x y dense_rank
- - ----------
1 1 1
2 {2,3} 2
1 4 3
Maybe this could be achieved in an easier way?
x
and order byy
within window function Postgres has to order it byx,y
so this is why your output is ordered like it is (x=1, y=4
comes beforex=2, y=2
) – Kamil G. Feb 9 at 16:50