PostgreSQL - how do I know which partition I'm in when using window functions?

Question

I've been using windows functions as they're very useful for my current requirement. row_number() is very handy for letting me know the row of the current partition. I've read the documentation on window functions and searched online, but can't find an answer to what I want. I'm hoping that someone here can tell if what I want is possible.

My question is this: is it possible to find out which partition within a window you are in, in a similar way that row_number() tells you which row within the partition you are in?

Basically, I want to know something like this, assuming 2 partitions in the window with 2 rows in each partition:

partition 1 row 1
partition 1 row 2
partition 2 row 1
partition 2 row 2

Any ideas, anyone?

Tim Biegeleisen · Accepted Answer · 2018-10-10 08:52:30Z

2

We can try using DENSE_RANK and ROW_NUMBER:

WITH yourTable AS (
    SELECT 'A' AS col1, 1 AS col2 UNION ALL
    SELECT 'A', 2 UNION ALL
    SELECT 'A', 3 UNION ALL
    SELECT 'B', 1 UNION ALL
    SELECT 'B', 2 UNION ALL
    SELECT 'B', 3
)

SELECT *,
    DENSE_RANK() OVER (ORDER BY col1) partition1,
    ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) partition2
FROM yourTable
ORDER BY
    col1, col2;

Demo

The general pattern here, for an arbitrary number of partitions, would be to use DENSE_RANK for the first N-1 partitions, and then ROW_NUMBER at the last partition. ROW_NUMBER would ensure that the last partition has 1,2,3,... as the sequence.

edited Oct 10, 2018 at 8:52

answered Oct 10, 2018 at 8:17

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Simon Kempster Over a year ago

Ah, interesting. I'll give that a go. Thanks.

Simon Kempster Over a year ago

Exactly what I needed. Thanks!

Collectives™ on Stack Overflow

PostgreSQL - how do I know which partition I'm in when using window functions?

1 Answer 1

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related