Hello Friends,
I encountered a behavior that confused me when using window function
and group aggregate at the same time.
simple table:
CREATE TABLE t (a int, b int);
add some data to represent the behavior:
insert into t values
(1, 39),
(1, 95),
(2, 48),
(3, 87),
(4, 19),
(4, 78),
(4, 53);
When I execute:
-- query #1
select
a,
row_number() over (partition by a order by a)
from t
group by a;
| a | row_number |
| --- | ---------- |
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
everything works as usual, but if I add desc sorting in over window:
-- query #2
select
a,
row_number() over (partition by a order by a desc)
from t
group by a;
| a | row_number |
| --- | ---------- |
| 4 | 1 |
| 3 | 1 |
| 2 | 1 |
| 1 | 1 |
the sorting of the entire table also follows the constraints (desc) in over.
This is what confuses me, I've tried many versions of pgsql, at least
including 11-16, and the behavior is consistent with the above; but I
feel that maybe the order by in the window function should not affect
the final result.
Also, I don't know if I can mention this, but the results of both
queries tested on mysql 8.0 are the same.
I searched the mailing list for a while but couldn't find it. If
anyone has already mentioned this, sorry, please feel free to point it
out.
