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...
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"
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).