Jump to content

Select MAX(date) from a group of records?


Recommended Posts

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

image.thumb.png.6fc0e45708617d4277e15c47c64781ea.png

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.

Link to post
Share on other sites

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)

 

Link to post
Share on other sites
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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.