progressive rank with redundant values

user_10950299 0
Viewed 32

Input Table:

seq id
1 A
2 A
3 B
4 B
5 A
7 C

Expected Output:

seq id rank
1 A 1
2 A 1
3 B 2
4 B 2
5 A 3
7 C 4

I've tried with rank() OVER(PARTITION BY id) but only gives me:

seq id rank
1 A 1
2 A 1
3 B 1
4 B 1
5 A 1
7 C 1

How can I do ? I didn't found with row_number or others window functiuns

postgresql sql
1 Answers

You can follow these two steps to solve your problem:

  • define a boolean value when the "id" changes, ordering rows on the "seq" field
  • compute a running sum over this flag that shows when id has changed
WITH cte AS (
    SELECT *, CASE WHEN id <> LAG(id) OVER(ORDER BY seq)
                   THEN 1 
                   ELSE 0
              END AS id_change
    FROM tab
)
SELECT seq,
       id,
       SUM(id_change) OVER(ORDER BY seq) +1 AS rn
FROM cte

We add +1 to the ranking because the first encountered "id" does not "change" with respect to previous values, but we still want to include it inside the ranking, hence every value gets shifted, as in the expected output.

Check the demo here.

user_124928900
Related