Question 384844 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Keep only columns combination with a minimum date in sql

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 at least have an entry for "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" t
inner join (
  select "COSTCENTER"
  from "data"
  group by "COSTCENTER"
  having max("CALMONTH") > 202301
) as s on s.COSTCENTER = t.COSTCENTER
Tags:

3

Answers


  1. 0

    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'
    Login or Signup to reply.
  2. 0

    A simple exists query should do it:

    SELECT *
FROM data AS t
WHERE EXISTS (
    SELECT *
    FROM data AS x
    WHERE costcenter = t.costcenter
    AND   kontonummer = t.kontonummer
    AND   calmonth >= 202301
)
    Login or Signup to reply.
  3. 0

    You can use a simple IN clause because they work with multiple columns in MySQL:

    SELECT *
FROM mytable
WHERE
  (costcenter, kontonummer) IN
    (SELECT costcenter, kontonummer
     FROM mytable
     WHERE calmonth >= 202301);

    The inner query fetches all pairs that satisfy your date condition, the outer one adds the other columns.

    Sample here: db<>fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search