This is my table ordered by code and date_time
| date | code |date_time |user_id | operation |
| ——– | ——– | —————— | —— | — |
| 20240301 | 5600 | 01/03/2024 4:14:16 | 1 | OP1 |
|20240301|5600| 01/03/2024 6:13:11 |2|OP2|
|20240301|5600| 01/03/2024 8:35:37 |3|OP1|
|20240301|5600| 01/03/2024 8:53:20 |4|OP1|
|20240301|5600| 01/03/2024 9:28:40 |5|OP1|
|20240301|5600| 01/03/2024 9:33:18 |6|OP3|
|20240301|5600| 01/03/2024 9:33:28 |6|OP1|
|20240301|5600| 01/03/2024 10:16:09 |7|OP4|
|20240301|5600| 01/03/2024 10:16:12 |7|OP5|
|20240301|5600| 01/03/2024 10:16:43 |7|OP1|
|20240301|5600| 01/03/2024 10:18:06 |8|OP2|
|20240301|5600| 01/03/2024 10:25:25 |8|OP1|
|20240301|5600| 01/03/2024 10:38:41 |8|OP1|
|20240301|5600| 01/03/2024 10:47:07 |9|OP1|
|20240301|5600| 01/03/2024 10:47:58 |9|OP1|
|20240301|5600| 01/03/2024 11:08:32 |10|OP1|
|20240301|5600| 01/03/2024 11:35:37 |7|OP1|
|20240301|5600| 01/03/2024 11:36:37 |7|OP1|
My new table and result I’d like is:
| date | code |date_time |user_id | operation |rank |
| ——– | ——– | —————— | —— | — |— |
|20240301|5600| 01/03/2024 4:14:16 |1|OP1| 1 |
|20240301|5600| 01/03/2024 6:13:11 |2|OP2| 1 |
|20240301|5600| 01/03/2024 8:35:37 |3|OP1| 1 |
|20240301|5600| 01/03/2024 8:53:20 |4|OP1| 1 |
|20240301|5600| 01/03/2024 9:28:40 |5|OP1| 1 |
|20240301|5600| 01/03/2024 9:33:18 |6|OP3| 1 |
|20240301|5600| 01/03/2024 9:33:28 |6|OP1| 2 |
|20240301|5600| 01/03/2024 10:16:09 |7|OP4| 1 |
|20240301|5600| 01/03/2024 10:16:12 |7|OP5| 2 |
|20240301|5600| 01/03/2024 10:16:43 |7|OP1| 3 |
|20240301|5600| 01/03/2024 10:18:06 |8|OP2| 1 |
|20240301|5600| 01/03/2024 10:25:25 |8|OP1| 1 |
|20240301|5600| 01/03/2024 10:38:41 |8|OP1| 1 |
|20240301|5600| 01/03/2024 10:47:07 |9|OP1| 1 |
|20240301|5600| 01/03/2024 10:47:58 |9|OP1| 2 |
|20240301|5600| 01/03/2024 11:08:32 |10|OP1| 1 |
|20240301|5600| 01/03/2024 11:35:37 |7|OP1| 1 |
|20240301|5600| 01/03/2024 11:36:37 |7|OP1| 2 |
Until user_id change put 1, when it doesn’t change, meaning has more date_time, put 1 2 3 etc as much date_time it has. I can’t reach that using dense_rank, row_number etc.. The solution will be fine in oracle sql or python as well. Thank you.
Te problem is that the order has to be as code and data_time and a block is where the user_id is not changing; when it changes so is a new block. Using partition is not working.
I tried:
RANK() OVER (PARTITION BY code ORDER BY date_time,user_id) AS RANK_NUM
DENSE_RANK() OVER (PARTITION BY code ORDER BY date_time,user_id) AS D_RANK_NUM
but it doesn’t work properly.
agg0131 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.