I have a below table.

I tried the below query.

select
   cid,
   oid,
   dense_rank() over (partition by oid order by order_date) as oid_history   
from 
    master.t1
where 
    cid = 1
order by 
    order_date asc;

Got the below output.

Expected output.

Thank you:)

CodePudding user response：

Can you try this one?

select
   cid,
   oid,
   order_date,
   dense_rank() over (partition by cid order by oid) as oid_history   
from 
    mytable -- master.t1
where 
    cid = 1
order by 
    order_date asc;

 ----- ----- ------------------------- ------------- 
| CID | OID |       ORDER_DATE        | OID_HISTORY |
 ----- ----- ------------------------- ------------- 
|   1 |  12 | 2020-07-01 13:19:16.235 |           1 |
|   1 |  12 | 2020-07-01 13:19:21.549 |           1 |
|   1 |  23 | 2020-07-27 13:00:18.446 |           2 |
|   1 |  34 | 2021-08-17 09:42:20.778 |           3 |
|   1 |  55 | 2022-08-01 13:37:53.340 |           4 |
|   1 |  55 | 2022-08-01 13:38:07.564 |           4 |
|   1 |  55 | 2022-08-01 13:38:28.201 |           4 |
 ----- ----- ------------------------- ------------- 

CodePudding user response：

something like this should work:

SELECT old, COUNT(*) FROM TABLENAME GROUP BY old ORDER BY old