CODEWITHSUNDEEP
sqlvertica
Simon Benavides
Simon Benavides

Reputation: 83

Adding a row number respecting the order of each row

I have a table like this

id, period, tag
1     1      A
1     2      A
1     3      B
1     4      A
1     5      A
1     6      A
2     1      A
2     2      B
2     3      B
2     4      B
2     5      B
2     6      A

I would like to add a new column with a ranking, respecting the order of the row given my column 'period' to obtain something like this

id, period, tag  rank
1     1      A     1
1     2      A     1
1     3      B     2
1     4      A     3
1     5      A     3
1     6      A     3
2     1      A     1
2     2      B     2
2     3      B     2
2     4      B     2
2     5      B     2
2     6      A     3

What can I do?

I try rank and dense_rank function without any success

Upvotes: 0

Views: 52

Answers (2)

marcothesane
marcothesane

Reputation: 6741

And another candidate for CONDITIONAL_CHANGE_EVENT() less code, and quite effective, too ...!

WITH
input(id,period,tag) AS (
          SELECT 1,1,'A'
UNION ALL SELECT 1,2,'A'
UNION ALL SELECT 1,3,'B'
UNION ALL SELECT 1,4,'A'
UNION ALL SELECT 1,5,'A'
UNION ALL SELECT 1,6,'A'
UNION ALL SELECT 2,1,'A'
UNION ALL SELECT 2,2,'B'
UNION ALL SELECT 2,3,'B'
UNION ALL SELECT 2,4,'B'
UNION ALL SELECT 2,5,'B'
UNION ALL SELECT 2,6,'A'
)
SELECT
  *
, CONDITIONAL_CHANGE_EVENT(tag) OVER(PARTITION BY id ORDER BY period) + 1 AS rank
FROM input;
-- out  id | period | tag | rank 
-- out ----+--------+-----+------
-- out   1 |      1 | A   |    1
-- out   1 |      2 | A   |    1
-- out   1 |      3 | B   |    2
-- out   1 |      4 | A   |    3
-- out   1 |      5 | A   |    3
-- out   1 |      6 | A   |    3
-- out   2 |      1 | A   |    1
-- out   2 |      2 | B   |    2
-- out   2 |      3 | B   |    2
-- out   2 |      4 | B   |    2
-- out   2 |      5 | B   |    2
-- out   2 |      6 | A   |    3
-- out (12 rows)
-- out 
-- out Time: First fetch (12 rows): 14.823 ms. All rows formatted: 14.874 ms

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

One method is a cumulative sum based on a lag():

select t.*,
       sum(case when prev_tag = tag then 0 else 1 end) over (partition by id order by period) as rank
from (select t.*, lag(tag) over (partition by id order by period) as prev_tag
      from t
     ) t;

Upvotes: 0

Related Questions