Article stock transaction table query Max Date gives multiple results on same timestamp, only need the last

Question

I've got an Article Stock transaction table, I only need the columns: Article, Date and Stock.

On the Same day, even on the same timestamp the table gives me multiple results for the same article, I only need the article with the latest date. It seems that the last in row is the article with the most recent Stock value. Bud how can I write this in a query that it only shows me the latest Date? (Blue row in Screenshot)

In my example I filtered it on only one article.

 SELECT artikel as Article, max(datum) as `Date`, bestand as 
 Stock  FROM data.HB001ARTIKELBESTANDSBEWEGUNG
 where datum > "2021-08-11 00:00:00" and datum < "2021-08-13 
00:00:00" and lager = 1 and artikel = '5201114970'
group by artikel, bestand, datum
order by artikel;

 

With kind regards,

Björn

I added the extra row with the auto increment, so now I get for every article with the latest date the highest row number. How can I filter now that i only get one result per article?

SELECT Article, max(CountDate), Stock, Nr  FROM data.STOCKCOUNT
where CountDate > "2021-08-11 00:00:00"  and CountDate < "2021- 
08-13 00:00:00" and Article IN ('5201114970', '1361001580')
group by Article, Stock, CountDate, Nr
order by Nr, CountDate desc, Article;

I added the extra row with the auto increment, so now I get for every article with the latest date the highest row number. How can I filter now that i only get one result per article filtered on the highest row number per article?

SELECT artikel as Article, max(datum) as Date, bestand as Stock, 
ROW_NUMBER() OVER(ORDER BY datum asc) as Nr  FROM 
data.HB001ARTIKELBESTANDSBEWEGUNG b where datum > "2021-08-11 
00:00:00" and datum < "2021-08-13 00:00:00" and lager = 1 and 
artikel in ('5201114970', '1361001580') group by artikel, 
bestand, datum order by datum desc, artikel

Answer 1

Please check the query. I can't test

SELECT Article, max(Nr), Stock, CountDate  FROM data.STOCKCOUNT
where CountDate > "2021-08-11 00:00:00"  and CountDate < "2021- 
08-13 00:00:00" and Article IN ('5201114970', '1361001580')
group by Article, Stock, CountDate
order by Nr DESC;