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
CodePudding user response：
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.