partition by - how to properly use in order to fetch related row

Question

:)

I use postgresql 13 and I was sure I understand how to use PARTITION BY but I guess I was wrong. I created the following example to illustrate my problem.

I created this table:

id, group_id,order_id,text
1   1        1        a1
2   1        2        b1
3   2        1        a2
4   2        2        b2
5   3        1        a3

so you've got unique id, group_id, and order in each group, and a text.

I wanted to select each row with order_id 1, so I have the first line of each group, and to also fetch the text of the last order_id of each group.

so I ran this query:

select id,group_id,order_id,the_text,
       last_value(the_text) over (partition by group_id order by order_id) last_text
from cdrs.my_test
where order_id=1;

and it returned

id,group_id,order_id,the_text,last_text
1,1,1,a1,a1
3,2,1,a2,a2
5,3,1,a3,a3

last_text and the_text are the same, it doesn't matter if I choose first_value() or last_value().

the last_text column should have returned with b1, b2, a3.

I was sure that when I get a row, I can use partition by to group that row with a rule, and get data from other rows in the group.

it really doesn't make sense to me why it doesn't work.

any information regarding this issue would be greatly appreciated.

Best Regards

Kfir

S-Man · Accepted Answer · 2020-11-23T16:47:27.793

2

demo:db<>fiddle

Window function are applied AFTER the WHERE clause. So, you cannot get the last value, because you just removed them. You can to this with a subquery:

SELECT
    *
FROM (
    SELECT
        *,
        first_value(order_id) OVER (PARTITION BY group_id ORDER BY order_id),
        first_value(t) OVER (PARTITION BY group_id ORDER BY order_id DESC)
    FROM
    t
) s
WHERE order_id = 1

edited Nov 23 '20 at 16:47

answered Nov 23 '20 at 16:35

S-Man

22,521
7
40
63

thank you. already tried that. results are exactly the same. – ufk Nov 23 '20 at 16:39
@ufk Ah, misunderstood you problem. Wrote a new answer. Hope that helps – S-Man Nov 23 '20 at 16:48
thank you that was the problem, I didn't know it was applied after the where :) – ufk Nov 23 '20 at 17:04

score 0 · Answer 2 · answered Nov 23 '20 at 16:37

I would use ROW_NUMBER here, twice:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY order_id) rn_min,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY order_id DESC) rn_max
    FROM cdrs.my_test
)

SELECT id, group_id, order_id, text
FROM cte
WHERE rn_min = 1 OR rn_max = 1
ORDER BY group_id, order_id;

score 0 · Answer 3 · answered Nov 23 '20 at 17:09

Heed the docs:

Note that first_value, last_value, and nth_value consider only the rows within the “window frame”, which by default contains the rows from the start of the partition through the last peer of the current row. This is likely to give unhelpful results for last_value and sometimes also nth_value. You can redefine the frame by adding a suitable frame specification (RANGE, ROWS or GROUPS) to the OVER clause. See Section 4.2.8 for more information about frame specifications.

So you need to add something like rows between unbounded preceding and unbounded following, but then you need to move the filter to an outer select or it will it remove the rows before they are considered part of the window.

select * from (select id,group_id,order_id,the_text,
       last_value(the_text) over (partition by group_id order by order_id rows between current row and unbounded following) last_text
from my_test)foo where order_id=1;

partition by - how to properly use in order to fetch related row

3 Answers3