CODEWITHSUNDEEP
sqlsql-serverrankingrank
emustyle
emustyle

Reputation: 3

SQL Ranking on existing sequence?

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?

Upvotes: 0

Views: 164

Answers (2)

Sahi
Sahi

Reputation: 1484

try this:

   DECLARE @mytab table (id INT,Sequence INT, [Event] VARCHAR(1))

    insert into @mytab
    SELECT 1,1,''
    union
    SELECT 1,2,'B'
    union
    SELECT 1,3,'B'
    union
    SELECT 1,4,'B'
    union
    SELECT 1,5,'B'
    union
    SELECT 1,6,'A'
    union
    SELECT 1,7,''
    union
    SELECT 1,8,'C'
    union
    SELECT 1,9,'C'
    union
    SELECT 1,10,'C'
    union
    SELECT 1,11,'C'
    union
    SELECT 1,12,'C'


    select *, DENSE_RANK()OVER( partition by id Order by id,[event],(SELECT NULL)) 
    from @mytab
    order by id,Sequence

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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;

Upvotes: 1

Related Questions