MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.7.39


CREATE TABLE `payment`
(`id` INT, `uid` INT, `currency` VARCHAR(3), `amount` INT);
DESCRIBE `payment`;


Field
Type
Null
Key
Default
Extra


id
int(11)
YES

null


uid
int(11)
YES

null


currency
varchar(3)
YES

null


amount
int(11)
YES

null



INSERT INTO `payment` (`id`,`uid`,`currency`,`amount`) VALUES
(1, 11, 'NTD', 800),
(2, 6, 'NTD', 590),
(3, 7, 'NTD', 1020),
(4, 55, 'USD', 24),
(5, 33, 'USD', 27),
(6, 2, 'USD', 25),
(7, 13, 'JPY', 3870),
(8, 10, 'JPY', 2980),
(9, 41, 'JPY', 3780);

Records: 9  Duplicates: 0  Warnings: 0


SELECT `id`,`uid`,`currency`,`amount`,@counter := IF(@current_currency = currency,@counter + 1,1) AS `counter`,@current_currency := `currency` AS `current_currency` FROM `payment` ORDER BY `currency`,`amount` DESC;


id
uid
currency
amount
counter
current_currency


7
13
JPY
3870
1
JPY

9
41
JPY
3780
1
JPY

8
10
JPY
2980
1
JPY

3
7
NTD
1020
1
NTD

1
11
NTD
800
1
NTD

2
6
NTD
590
1
NTD

5
33
USD
27
1
USD

6
2
USD
25
1
USD

4
55
USD
24
1
USD


SELECT `id`,`uid`,`currency`,`amount`,@counter := IF(@current_currency = currency,@counter + 1,1) AS `counter`,@current_currency := `currency` AS `current_currency` FROM `payment` ORDER BY `currency`,`amount` DESC;


id
uid
currency
amount
counter
current_currency


7
13
JPY
3870
1
JPY

9
41
JPY
3780
2
JPY

8
10
JPY
2980
3
JPY

3
7
NTD
1020
1
NTD

1
11
NTD
800
2
NTD

2
6
NTD
590
3
NTD

5
33
USD
27
1
USD

6
2
USD
25
2
USD

4
55
USD
24
3
USD

Field	Type	Null	Default
id	int(11)	YES	null
uid	int(11)	YES	null
currency	varchar(3)	YES	null
amount	int(11)	YES	null

id	uid	currency	amount	counter	current_currency
7	13	JPY	3870	1	JPY
9	41	JPY	3780	1	JPY
8	10	JPY	2980	1	JPY
3	7	NTD	1020	1	NTD
1	11	NTD	800	1	NTD
2	6	NTD	590	1	NTD
5	33	USD	27	1	USD
6	2	USD	25	1	USD
4	55	USD	24	1	USD