Search code examples
sqlhivewindow-functionsdense-rank

dense_rank ()over ID values - How to rank ID numbers by order instead of value

I have a situation where there are transactions ordered by end times "ts_end_utc" and each transaction has an agent assigned to it. I want to do add a ranking to each agent (they change throughout the transactions) while following the time sequence. Unfortunately when I use dense_rank, it does the ranking based on the value of the agent ID and does not follow the ordering of "ts_end_utc" in order. Any suggestions?

Ideally I would want the "agent_number" which is my ranking column to start with 1 instead of 2 and then switch over to 2 on the 8th row

dense_rank() over(partition by cte_v3.id_ticket_anchor order by agent) agent_number,

agent_rank agent ts_end_utc id_ticket_anchor
2 200 16:53:28 95300807
2 200 17:01:36 95300807
2 200 17:06:26 95300807
2 200 18:09:58 95300807
2 200 18:17:50 95300807
2 200 18:20:14 95300807
2 200 18:51:52 95300807
3 201 18:53:24 95300807
3 201 19:13:48 95300807
1 199 19:15:47 95300807

Desired Result

agent_rank agent ts_end_utc id_ticket_anchor
1 200 16:53:28 95300807
1 200 17:01:36 95300807
1 200 17:06:26 95300807
1 200 18:09:58 95300807
1 200 18:17:50 95300807
1 200 18:20:14 95300807
1 200 18:51:52 95300807
2 201 18:53:24 95300807
2 201 19:13:48 95300807
3 199 19:15:47 95300807

Second Scenario - Current Result

agent_rank agent ts_end_utc id_ticket_anchor
1 200 16:53:28 95300807
1 200 17:01:36 95300807
1 200 17:06:26 95300807
1 200 18:09:58 95300807
1 200 18:17:50 95300807
1 200 18:20:14 95300807
1 200 18:51:52 95300807
2 201 18:53:24 95300807
2 201 19:13:48 95300807
3 199 19:15:47 95300807
1 200 19:16:55 95300807

Second Scenario - Desired Result

agent_rank agent ts_end_utc id_ticket_anchor
1 200 16:53:28 95300807
1 200 17:01:36 95300807
1 200 17:06:26 95300807
1 200 18:09:58 95300807
1 200 18:17:50 95300807
1 200 18:20:14 95300807
1 200 18:51:52 95300807
2 201 18:53:24 95300807
2 201 19:13:48 95300807
3 199 19:15:47 95300807
4 200 19:16:55 95300807

enter image description here

Solution

  • Since there are different ts_end_utc for a single agent within a id_ticket_anchor I have first calculated minimum ts_end_utc for each id_ticket_anchor wise agent with min(ts_end_utc)over(partition by id_ticket_anchor, agent) window function.

    Then use in order by clause of dense_rank(). There might be more than one agent with same minimum ts_end_utc, so agent is also used in order by clase.

    DB-Fiddle:

     create table cte_v3(agent int, ts_end_utc time, id_ticket_anchor int);

 insert into  cte_v3 values(200 ,'16:53:28','95300807');
 insert into  cte_v3 values(200 ,'17:01:36','95300807');
 insert into  cte_v3 values(200 ,'17:06:26','95300807');
 insert into  cte_v3 values(200 ,'18:09:58','95300807');
 insert into  cte_v3 values(200 ,'18:17:50','95300807');
 insert into  cte_v3 values(200 ,'18:20:14','95300807');
 insert into  cte_v3 values(200 ,'18:51:52','95300807');
 insert into  cte_v3 values(201 ,'18:53:24','95300807');
 insert into  cte_v3 values(201 ,'19:13:48','95300807');
 insert into  cte_v3 values(199 ,'19:15:47','95300807');

    Query:

     With cte as 
 (
 select agent,ts_end_utc,id_ticket_anchor,
 min(ts_end_utc)over(partition by id_ticket_anchor, agent) min_ts_end_utc
 from cte_v3
 )
 select dense_rank()over(partition by id_ticket_anchor order by min_ts_end_utc,agent)agent_rank,
 agent,ts_end_utc,id_ticket_anchor
 from cte

    Output:

    agent_rank agent ts_end_utc id_ticket_anchor
    1 200 16:53:28.0000000 95300807
    1 200 17:01:36.0000000 95300807
    1 200 17:06:26.0000000 95300807
    1 200 18:09:58.0000000 95300807
    1 200 18:17:50.0000000 95300807
    1 200 18:20:14.0000000 95300807
    1 200 18:51:52.0000000 95300807
    2 201 18:53:24.0000000 95300807
    2 201 19:13:48.0000000 95300807
    3 199 19:15:47.0000000 95300807

    db<fiddle here