MySQL subquery for selecting latest items returns to much entities

Question

I'm busy with this problem for hours now. I hope you can help me.
I have a table which contains some articles on different inventory locations. There's also a column which describes the date when the current state was noticed.

I try to get a query which returns the entitys of - a specific article - for every inventory location - only one entry for every inventory location, but it should be the latest entry of a specific date.

So, this is my table:

CREATE TABLE `article_stock` (
  `id` bigint(20) NOT NULL,
  `stock` double NOT NULL,
  `date` datetime DEFAULT NULL,
  `inventory_location` varchar(255) DEFAULT NULL,
  `article` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_krgmyglif194cjh9t1ndmse6n` FOREIGN KEY (`article`)
REFERENCES `article` (`article`)
);

So, I tried several approaches. But I can't solve my problem. One more example:

I use this query:

SELECT * FROM article_stock WHERE article_stock.date <= "2015-10-12 00:00:00" AND article_stock.article = 5656

id      stock   date                inventory_location  article
6310    1058.68 2015-10-10 00:00:00 A64                 5656
6311    561.08  2015-10-11 00:00:00 A64                 5656
6312    140.92  2015-10-12 00:00:00 A64                 5656
6314    20.06   2015-10-10 00:00:00 K16                 5656
6315    600     2015-10-11 00:00:00 K16                 5656

I want to get the IDs 6312 and 6315. Can someone help me? :-(

Thank you! :-)

EDIT: It seems like it's the same problem as described here:Retrieving the last record in each group But that's not true. The question there is to retreive the latest record. But I want to get the latest record of a specific date FOR EVERY grouped element... Let me explain:

I changed the most popular solution for fitting in my situation:

select
    a.*
from
    article_stock a
    inner join 
        (select inventory_location, max(date) as datecol 
         from article_stock
         WHERE date <= "2015-10-11 00:00:00"
         group by inventory_location) as b
        ON (a.inventory_location = b.inventory_location
        AND a.date = b.datecol)
WHERE article = 5656;

It returns two rows:

id      stock   date                inventory_location  article
6311    561.08  2015-10-11 00:00:00 A64                 5656
6315    600     2015-10-11 00:00:00 K16                 5656

But when I change the date in the where clause to 2015-10-12 it returns only one single row:

id      stock   date                inventory_location  article
6312    140.92  2015-10-12 00:00:00 A64                 5656

But the correct solution would be:

id      stock   date                inventory_location  article
6312    140.92  2015-10-12 00:00:00 A64                 5656
6315    600     2015-10-11 00:00:00 K16                 5656

I can't assume that every "inventory_location" change happened on the same date! :-(

Possible duplicate of [Retrieving the last record in each group](http://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group) — Strawberry, Oct 12 '15 at 13:27
The question there is to retrieve the latest record in each group. And even if it wasn't, there are thousands of similar questions on SO. Just look at the 'Related' column over there ---->>>>> — Strawberry, Oct 12 '15 at 14:27

score 1 · Answer 1 · answered Oct 12 '15 at 14:10

Think your later query is almost there, but you need to check the article number in the sub query as well (ie, I presume the max date for an inventory location may be different between different articles):-

SELECT a.* 
FROM article_stock a
INNER JOIN
(
    SELECT article, inventory_location, MAX(`date`) AS max_date
    FROM article_stock 
    WHERE `date` <= "2015-10-12 00:00:00" 
    GROUP BY article, inventory_location
) b
ON a.article = b.article
AND a.inventory_location = b.inventory_location
AND a.`date` = b.max_date
WHERE a.article = 5656

Thanks! That was very helpful! – Roman B Oct 22 '15 at 09:11 — Roman B, Oct 22 '15 at 09:11

MySQL subquery for selecting latest items returns to much entities

1 Answers1