Query based on some data multiple compare

Table:

How do i bring the period when customers have max cc_limit? If the cc_limits same for customer for more than one month, take the period with highest year-month data

Answer

Use row_number() window function,Below should work.

  select *
     from 
        (select cust_no,year_month,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 tablename order by cust_no,year_month desc) tab
      where rownum = 1

EDIT : To clarify why order by should be in the inline view.

Scenario : When there are duplicate records for same cc_limit and cust_no,the requirement is to retrieve highest yearmonth record.

The default order of data retrieval for any table is by the order they are inserted unless a filter caused an index to be used to filter data.

Suppose 201912 record is inserted first followed by 201911(Not possible practically though, may be during a database reorganization the order of insertion got flipped) then without order by clause would return 201911 record instead of 201912.

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 201912 record inserted first and then 201912 and has same cc_limit.

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);

When the query is executed without order by in the inline view,

 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

The yearmonth returned for cust_no 1 is 201911 instead of 201912

But the query with order by clause would ensure that latest yearmonth record being returned,

 select *
         from 
            (select cust_no,year_month,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 tablename order by cust_no,year_month desc) tab
          where rownum = 1

I have demonstrated the whole behaviour in DBFIDDLE link for MySQL8.0 – https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=03b06094d10a0daf7732f0cb19798262