Sorry but i'm stucking at this given table tbl_inventar:

I would like to get the artikelID and the value of istAnzahl associated with the max(date).

Like this:

I've tried the following, but somehow I'm just getting desperate.

Select
  tbl_inventar.artikelID,
  tbl_inventar.istAnzahl,
  tbl_inventar.sollAnzahl,
  Max(tbl_inventar.date) As Max_date
From
  tbl_inventar
Group By
  tbl_inventar.artikelID

But this doesn't give the associated values for istAnzahl

CodePudding user response：

On older MySQL version which do not support windows function you could use a subquery to get the max date per each artikelID and you need to add on the join condition. Based on there could not be more than one artikelID (with different values ​​for istAnzahl and date) with the following query you will alyways get the latest record based on date:

select t.artikelID,
       t.istAnzahl,
       t.sollAnzahl,
       t.`date`
from test t
inner join (select artikelID , max(`date`) as max_date
            from test
            group by artikelID
          ) as max_dt on max_dt.artikelID=t.artikelID and max_dt.max_date=t.`date`;

On MySQL versions which do support windows function :

select artikelID,
       istAnzahl,
       sollAnzahl,
       `date`
from (select *, row_number() over(partition by artikelID order by `date` desc ) as max_dt
      from test
    ) tbl 
where tbl.max_dt=1;

https://dbfiddle.uk/Ej4sxL4O