I have a data set which I'm trying to apply a secondary rank to and am unsure how to go about it.
The data set looks like:
ID Sequence Event
xxx 1
xxx 2 B
xxx 3 B
xxx 4 B
xxx 5 B
xxx 6 A
xxx 7
xxx 8 C
xxx 9 C
xxx 10 C
xxx 11 C
xxx 12 C
I've tried a few Ranking/Partitioning combinations and the closest I've gotten has been something a long the lines of:
DENSE_RANK() OVER (Partition BY ID ORDER BY ID, Event)
However when I do this the resulting ranking ranks the event in an unordered fashion, I understand it is doing so alphabetically by the event:
ID Sequence Event Event Sequence
xxx 1 1
xxx 2 B 3
xxx 3 B 3
xxx 4 B 3
xxx 5 B 3
xxx 6 A 2
xxx 7 1
xxx 8 C 4
xxx 9 C 4
xxx 10 C 4
xxx 11 C 4
xxx 12 C 4
I attempted to add the Sequence into the Order by, but it no longer grouped the event the way I wanted them and would simply emulate the sequence series.
What I'm hoping for is something along the lines of:
ID Sequence Event Event Sequence
xxx 1 1
xxx 2 B 2
xxx 3 B 2
xxx 4 B 2
xxx 5 B 2
xxx 6 A 3
xxx 7 1
xxx 8 C 4
xxx 9 C 4
xxx 10 C 4
xxx 11 C 4
xxx 12 C 4
Is this possible?
If I understand correctly, you can use the minimum of the id value for the ordering:
select t.*, dense_rank() over (order by min_sequence) as event_sequence
from (select t.*, min(sequence) over (partition by event) as min_sequence
from t
) t
order by t.id;