2

I have a log file I need to either rank (but treating sequential and equal rows as ties), or merge sequential equal rows (based on specific column). My table looks like below, The Start and Stop are all being sequential (within the same ID window)

ID Start Stop Value
1  0     1    A
1  1     2    A
1  2     3    A
1  3     4    B
1  4     5    B
1  5     6    A
2  3     4    A

I have two approches to get what I need.

Approach 1: Rank (treating sequential rows with equal values in "Value" as ties) and using ID as partition. This should give the output below. But how do I do the special rank: Treating sequential rows with equal values in "Value" as ties.

Select *,
rank() OVER (partition by id order by start, stop) as Rank,
XXX as SpecialRank
from Table

ID Start Stop Value Rank SpecialRank
1  0     1    A     1    1
1  1     2    A     2    1
1  2     3    A     3    1
1  3     4    B     4    2
1  4     5    B     5    2
1  5     6    A     6    3
2  3     4    A     1    1

Approach 2: Merge sequential rows with equal values in "Value". This will shall create a table like below.

ID Start Stop Value
1  0     3    A
1  3     5    B
1  5     6    A
2  3     4    A

I don't know if this helps, but I have also a nextValue column that might help in this

ID Start Stop Value NextValue
1  0     1    A     A
1  1     2    A     A
1  2     3    A     B
1  3     4    B     B
1  4     5    B     A
1  5     6    A     A
2  3     4    A     ...

Example-table:

CREATE TABLE #Table ( id int, start int, stop int, Value char(1), NextValue char(1));
INSERT INTO #Table values (1,0, 1, 'A', 'A');
INSERT INTO #Table values (1,1, 2, 'A', 'A');
INSERT INTO #Table values (1,2, 3, 'A', 'B');
INSERT INTO #Table values (1,3, 4, 'B', 'B');
INSERT INTO #Table values (1,4, 5, 'B', 'A');
INSERT INTO #Table values (1,5, 6, 'A', 'A');
INSERT INTO #Table values (2,3, 4, 'A', null);
Pär Adeen
  • 23
  • 4
  • I'm confused, what is your question here exactly? – Thom A Feb 07 '19 at 12:20
  • @Larnu The table logs changes of Value. But sometimes there's a log even though the Value has not changed. I have a handfull of measures I need to do on this table, but having the Values beeing repeated in sequential rows makes it very difficult. – Pär Adeen Feb 07 '19 at 22:00

1 Answers1

1

Use a self join to an aggregate subquery from the full set, e.g.

with rankTable (id, value) as ( select 1, 'A' union all select 1, 'A' union all select 1, 'B' union all select 2, 'A')

select t2.* from rankTable t1 join (
    select id, value, rank() over (partition by id order by value) as specialRank from
    (
    select distinct id, value
            from rankTable
    ) t) t2 on t2.id  =t1.id and t2.value = t1.value


id  value   specialRank
1   A   1
1   A   1
1   B   2
2   A   1
Janine Rawnsley
  • 1,240
  • 2
  • 10
  • 20