Select records columns where a minimum date exists for pair of columns

Question

I am having an sql view "data", which looks like the following:

CALMONTH	COSTCENTER	KONTONUMMER	FIELDNM003
202212	Alpha	123	10
202301	Alpha	123	20
202210	Alpha	223	50
202307	Beta	123	10

I want to keep only the combinations of "COSTCENTER" and "KONTONUMMER" which have one or more entrries having "CALMONTH" >= 202301. The desired output would be:

CALMONTH	COSTCENTER	KONTONUMMER	FIELDNM003
202212	Alpha	123	10
202301	Alpha	123	20
202307	Beta	123	10

Right now I am only able, to do it only for one column "COSTCENTER":

SELECT "t"."CALMONTH", "t"."COSTCENTER", "t"."KONTONUMMER", "t"."FIELDNM003"
FROM "data" AS t
INNER JOIN (
  SELECT "COSTCENTER"
  FROM "data"
  GROUP BY "COSTCENTER"
  HAVING MAX("CALMONTH") > 202301
) AS s ON s.COSTCENTER = t.COSTCENTER

Answer 1

You should identify any COSTCENTER and Kontonummer pairs that have at least one entry where "CALMONTH" >= 202301. After finding these pairs, join them with your table to retrieve all corresponding rows :

SELECT t.*
FROM mytable t
inner join (
  select COSTCENTER, Kontonummer
  from mytable
  where CALMONTH >= 202301
  group by COSTCENTER, Kontonummer
) as s on s.COSTCENTER = t.COSTCENTER
       and s.Kontonummer = t.Kontonummer;

Demo here

If you are using mysql 8 or higher, This is an other way to do it using window function MAX() :

select CALMONTH, COSTCENTER, Kontonummer, FIELDNM003
from (
  select *, max(CALMONTH) over (partition by COSTCENTER, Kontonummer) as max_CALMONTH
  from mytable
) as s
where max_CALMONTH >= '202301'