CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-group
Engl12
Engl12

Reputation: 139

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.

Upvotes: 1

Views: 310

Answers (2)

talegna
talegna

Reputation: 2403

What I believe you need to look into is the HAVING clause.

Here is an example:

SELECT 
    a.PIECE_NO
    , o.FROM_POS
FROM 
    acoll a
    INNER JOIN odd o ON a.PIECE_NO = o.PIECE 
GROUP BY  
    a.PIECE_NO
    , o.FROM_POS
HAVING 
    o.DATE = MAX(o.DATE)

Upvotes: 0

Andomar
Andomar

Reputation: 238086

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.

Upvotes: 2

Related Questions