Auto Increment by Column and by Group-CodePudding

I search everywhere. I can't find my want.

My current Schema with data | Table Name ["mini_sales"]

id (auto increment)	transactionid	totalpayment	totalchange	date
1	NULL	1000	20	08/01/2022 13:52:00
2	NULL	1000	20	08/01/2022 13:52:00
3	NULL	1000	20	08/01/2022 13:52:00
4	NULL	200	10	08/01/2022 13:56:00
5	NULL	200	10	08/01/2022 13:56:00
6	NULL	300	10	08/01/2022 13:58:00

I want table_schema like this:

id (auto increment)	transactionid	totalpayment	totalchange	date
1	1	1000	20	08/01/2022 13:52:00
2	1	1000	20	08/01/2022 13:52:00
3	1	1000	20	08/01/2022 13:52:00
4	2	200	10	08/01/2022 13:56:00
5	2	200	10	08/01/2022 13:56:00
6	3	300	10	08/01/2022 13:58:00

I tried creating one [Select], but I can't comprehend it well.

select * 
from mini_sales 
GROUP by totalpayment, totalchange, date 
order by id, date

CodePudding user response：

you can use the window function DENSE_RANK#

SELECT
`id`,DENSE_RANK() OVER(ORDER BY `date` ) `transactionid`, `totalpayment`, `totalchange`, `date`
FROM Table1

id | transactionid | totalpayment | totalchange | date               
-: | ------------: | -----------: | ----------: | :------------------
 1 |             1 |         1000 |          20 | 2022-08-01 15:52:00
 2 |             1 |         1000 |          20 | 2022-08-01 15:52:00
 3 |             1 |         1000 |          20 | 2022-08-01 15:52:00
 4 |             2 |          200 |          10 | 2022-08-01 15:56:00
 5 |             2 |          200 |          10 | 2022-08-01 15:56:00
 6 |             3 |          300 |          10 | 2022-08-01 15:58:00

db<>fiddle here