MySQL - Select a single column with the maximum time stamp

advertisements

I'm trying to prepare a query and I'm having a hard time with it. I need some MySQL gurus to help please...

Take the following table as an example...

CREATE TABLE order_revision ( id int(11) NOT NULL, parent_order_id int(11) NOT NULL, user_id int(11) DEFAULT NULL, sub_total decimal(19,4) NOT NULL DEFAULT '0.0000', tax_total decimal(19,4) NOT NULL DEFAULT '0.0000', status smallint(6) NOT NULL DEFAULT '1', created_at int(11) NOT NULL, updated_at int(11) DEFAULT NULL )

I need a query to select all unique 'parent_order_id' with the max 'updated_at' value. This query should return all rows that have unique 'parent_order_id's based on the max timestamp of the 'updated_at' column.

In other words, each row returned should have an unique 'parent_order_id' and be the maximum timestamp of the'updated_at' column.

Basically this query would find the latest "order revision" for each "parent order"


You mean:

SELECT parent_order_id,max(updated_at) FROM order_revision GROUP BY parent_order_id

For MySQL, the GROUP BY-clause isn't even necessary, nevertheless I would include it for clarification (and most other SQL-conform servers require it).