MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


create table test  (cust_no INT,yearmonth varchar(10),cc_limit INT,
cc_tot_risk INT,cc_period_risk INT,cc_ins_risk INT)
# cust_no 1 has 201911 record inserted first and then 201912
insert into test values(1,201911,16000,16,16,0);
insert into test values(2,201912,15000,16,16,0);
insert into test values(1,201912,16000,16,16,0);
insert into test values(3,201912,17000,16,16,0);
insert into test values(4,201911,10000,16,16,0);
select cust_no,yearmonth,cc_limit,cc_tot_risk,cc_period_risk,cc_ins_risk,
        row_number() over(partition by cust_no order by cc_limit desc) rownum
         from test order by cust_no,yearmonth desc
         


cust_no
yearmonth
cc_limit
cc_tot_risk
cc_period_risk
cc_ins_risk
rownum


1
201912
16000
16
16
0
2

1
201911
16000
16
16
0
1

2
201912
15000
16
16
0
1

3
201912
17000
16
16
0
1

4
201911
10000
16
16
0
1


#Incorrect output - For cust_no 1 yearmonth is returned as 201911 instead of 201912
# Here order by Clause is removed
 select *
     from 
        (select cust_no,yearmonth,cc_limit,cc_tot_risk,cc_period_risk,cc_ins_risk,
        row_number() over(partition by cust_no order by cc_limit desc) rownum
         from test) tab
      where rownum = 1


cust_no
yearmonth
cc_limit
cc_tot_risk
cc_period_risk
cc_ins_risk
rownum


1
201911
16000
16
16
0
1

2
201912
15000
16
16
0
1

3
201912
17000
16
16
0
1

4
201911
10000
16
16
0
1


truncate table test;
# cust_no 1 has 201912 record inserted first and then 201912
insert into test values(1,201911,16000,16,16,0);
insert into test values(2,201912,15000,16,16,0);
insert into test values(1,201912,16000,16,16,0);
insert into test values(3,201912,17000,16,16,0);
insert into test values(4,201911,10000,16,16,0);
select cust_no,yearmonth,cc_limit,cc_tot_risk,cc_period_risk,cc_ins_risk,
        row_number() over(partition by cust_no order by cc_limit desc) rownum
         from test order by cust_no,yearmonth desc


cust_no
yearmonth
cc_limit
cc_tot_risk
cc_period_risk
cc_ins_risk
rownum


1
201912
16000
16
16
0
2

1
201911
16000
16
16
0
1

2
201912
15000
16
16
0
1

3
201912
17000
16
16
0
1

4
201911
10000
16
16
0
1


#Incorrect output - For cust_no 1 yearmonth is returned as 201911 instead of 201912
 select *
     from 
        (select cust_no,yearmonth,cc_limit,cc_tot_risk,cc_period_risk,cc_ins_risk,
        row_number() over(partition by cust_no order by cc_limit desc) rownum
         from test order by cust_no,yearmonth desc) tab
      where rownum = 1


cust_no
yearmonth
cc_limit
cc_tot_risk
cc_period_risk
cc_ins_risk
rownum


1
201911
16000
16
16
0
1

2
201912
15000
16
16
0
1

3
201912
17000
16
16
0
1

4
201911
10000
16
16
0
1
cust_no	yearmonth	cc_limit	cc_tot_risk	cc_period_risk	rownum
1	201912	16000	16	16	2
1	201911	16000	16	16	1
2	201912	15000	16	16	1
3	201912	17000	16	16	1
4	201911	10000	16	16	1