Select only the last date when two columns are duplicate

Question

I need to select seven columns from three different tables, only when one of the columns has a particular value. I also need to select only the last date when two columns (TAGNAME and TAGNUMMER) are both duplicate. I'm using the following code:

select c.AKEY, c.AKT_DATUM, c.TAGNAME, c.TAGNUMMER, 
       cd.TEILANLAGEN_ID, x.TP_GSAP_KZ, c.KLASSEN_ID
from T0EM01 c, T0EM03 x, T0AD07 cd
where cd.TEILANLAGEN_ID = '219A'
inner join
(select c.TAGNAME and c.TAGNUMMER max(C.AKT_DATUM)
where T0EM01 c c.TAGNAME and T0EM01 c c.TAGNUMMER = m.max_date

Up to where cd.TEIANLAGEN_ID = '219A' it works fine (but there are over 2 million rows).

How can I filter so that when both TAGNAME and TAGNUMMER are repeated in two or more rows I only select the latest date?

Answer 1

"Over 2 million rows" could be less if you properly joined those 3 tables. The way you put it, you're producing Cartesian join and got way too many rows.

from t0em01  c,
     t0em03  x,
     t0ad07  cd

I have no idea how are they to be joined to each other so I'm just guessing; you should know.

As of the "max date value", one option might be to use a subquery, also properly joined to other table(s). Once again, I don't know how exactly to join them.

Improve it:

select c.akey,
       c.akt_datum,
       c.tagname,
       c.tagnummer,
       cd.teilanlagen_id,
       x.tp_gsap_kz,
       c.klassen_id
from t0em01 c join t0em03 x on x.id = c.id          --> I'm just 
              join t0ad07 cd on cd.id = c.id        --  guessing here
where cd.teilanlagen_id = '219A'
  and c.akt_datum = (select max(c1.akt_datum)       --> subquery, to return
                     from t0em01 c1                 --  only the MAX date value
                     where c1.tagname = c.tagname
                       and c1.tagnummer = c.tagnummer
                    );