Search code examples
mysqlsqlgreatest-n-per-group

Filtering Max Date from the second table of a query

I am trying to run a query from two tables, one that doesn't have dates, and one that does.

I only want the latest date recorded result from the second table as it would make the data incorrect.

I am using the following query in MYSQL.

$sql = "SELECT acoll.PIECE_NO, odd.FROM_POS
FROM acoll, odd
WHERE acoll.PIECE_NO = odweld.PIECE and odd.DATE IN

    (SELECT
       MAX(DATE)
     FROM
       odd
       )

GROUP BY odd.FROM_POS" ;

It doesn't seem to be giving me any result, I've been trying lots of different ways for hours, and can't seem to crack it,

Sorry if this is a really amateur question, I'm brand new to mysql and php.

Please let me know if you need any more info.

Solution

  • One way is a filtering join:

    select  *
from    acoll t1
join    odd t2
on      t1.piece_no = t2.piece
join    (
        select  piece
        ,       max(date) as max_date
        from    odd
        group by
                piece
        ) filter
on      filter.piece = t2.piece
        and filter.max_date = t2.date

    Or a correlated subquery:

    select  *
from    acoll t1
join    odd t2
on      t1.piece_no = t2.piece
where   t2.date = 
        (
        select  max(date)
        from    odd t2_max
        where   t2_max.piece = t2.piece
        )

    More solutions can be found in the tag.