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
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;
If you are using MySQL 8 or higher, there is another way to do it using the 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';