ROW_NUMBER() with DISTINCT

Question

I've got a table of ticket assignments showing the different groups a ticket is transferred to before its resolved. Here is a simplified table:

asgn_grp | date   | ticket_id
---------|--------|----------
       A | 1-1-15 | 1
       A | 1-2-15 | 1
       B | 1-3-15 | 1
       A | 1-1-15 | 2
       C | 1-2-15 | 2
       B | 1-3-15 | 2
       C | 1-1-15 | 3
       B | 1-2-15 | 3

I need to get a count of the second distinct group that a ticket was assigned to, meaning I want to know once a ticket is transferred out of the group its in, internal transfers don't count. So the second distinct group for ticket 1 is B, ticket 2 is C, ticket 3 is B. I need to get a count of these, so the end result I need is

asgn_grp | count
---------|-------
       B | 2
       C | 1

I've tried

SELECT distinct top 2 asgn_grp, ROW_NUMBER() OVER (ORDER BY date) 

As my sub-query and pulling the second one out of that, but when I add the ROW_NUMBER() it messes up my distinct. If I pull the ROW_NUMBER() out of the sub-query, I have now way to order my values to ensure I get the second one after I DISTINCT the list.

Also, let me know if I was unclear about anything.

Solution

Instead of using distinct, try using group by twice.

select asgn_grp, count(*) from (
    select * , row_number() over (partition by ticket_id order by min_date) rn
    from (
        select asgn_grp, ticket_id, min(date) min_date
        from Table1 group by asgn_grp, ticket_id
    ) t1 
) t2 where rn = 2
group by asgn_grp;

http://sqlfiddle.com/#!3/a0d1e

The derived table t1 contains every unique asgn_grp for each ticket_id along with the minimum date of each asgn_grp. For the sample data t1 has the following rows:

ASGN_GRP    TICKET_ID   MIN_DATE
A   1   January, 01 2015 00:00:00+0000
B   1   January, 03 2015 00:00:00+0000
A   2   January, 01 2015 00:00:00+0000
B   2   January, 03 2015 00:00:00+0000
C   2   January, 02 2015 00:00:00+0000
B   3   January, 02 2015 00:00:00+0000
C   3   January, 01 2015 00:00:00+0000

The outer query then uses row_number() to number each asgn_grp within a ticket_id by its min_date and generates the following for t2

ASGN_GRP    TICKET_ID   MIN_DATE    RN
A   1   January, 01 2015 00:00:00+0000  1
B   1   January, 03 2015 00:00:00+0000  2
A   2   January, 01 2015 00:00:00+0000  1
C   2   January, 02 2015 00:00:00+0000  2
B   2   January, 03 2015 00:00:00+0000  3
C   3   January, 01 2015 00:00:00+0000  1
B   3   January, 02 2015 00:00:00+0000  2

This table is filtered for RN = 2 and is grouped by asgn_grp to get the count for each asgn_grp.