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 AA.id,AA.uid,AA.amount,
@rowno := if(@grp = `currency`, @rowno + 1, 1) as idx,
@grp := `currency` as currency
from (select * from payment order by `currency`, amount DESC) AA
cross join (select @rowno := 0, @grp := null) BB


id
uid
amount
idx
currency


7
13
3870
1
JPY

9
41
3780
2
JPY

8
10
2980
3
JPY

3
7
1020
1
NTD

1
11
800
2
NTD

2
6
590
3
NTD

5
33
27
1
USD

6
2
25
2
USD

4
55
24
3
USD


select CC.id,CC.uid,CC.currency,CC.amount
from (
select AA.id,AA.uid,AA.amount,
@rowno := if(@grp = `currency`, @rowno + 1, 1) as idx,
@grp := `currency` as currency
from (select * from payment order by `currency`, amount DESC) AA
cross join (select @rowno := 0, @grp := null) BB) CC
where CC.idx = 2


id
uid
currency
amount


9
41
JPY
3780

1
11
NTD
800

6
2
USD
25

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	amount	idx	currency
7	13	3870	1	JPY
9	41	3780	2	JPY
8	10	2980	3	JPY
3	7	1020	1	NTD
1	11	800	2	NTD
2	6	590	3	NTD
5	33	27	1	USD
6	2	25	2	USD
4	55	24	3	USD