Select MAX(date) from a group of records?

[mongoose00318]

I'm trying to select all of the most recent records for each 'order_id' with the following table structure:

So with that example above I would want record 6840 because it is the newest record for order 5157.

SELECT
    pda.*
FROM
    production_data_archive pda
INNER JOIN (
    SELECT MAX(insert_time) as MAX_insert_time
    FROM production_status_archive pda2
    GROUP BY order_id
) ON pda.order_id = pda2.order_id

I'm not sure what I'm doing wrong with my query but I'm getting an error.

[Barand]

You need to match aginst the subquery on id and time.

Table subqueries require an alias (as virtual table name)

try

SELECT
    pda.*
FROM
    production_data_archive pda
INNER JOIN (
    SELECT 
        order_id
        MAX(insert_time) as insert_time
    FROM production_status_archive 
    GROUP BY order_id
) pda2  USING (order_id, inser_time)

 

[mongoose00318]

I tried this and I think it gave me what I wanted?

SELECT *, MAX(insert_time) FROM production_data_archive GROUP BY order_id

 

[mongoose00318]

Maybe I didn't need the join?

[Barand]

3 hours ago, mongoose00318 said:

I tried this and I think it gave me what I wanted?

If it did it was only by luck. The information returned for each id could come from any of the records with that id, not necessarily the one with the max value.

[mongoose00318]

@Barand Hmm...okay thanks for the heads up. I'll keep that in mind next time I have to do something similar.