I have read other threads on this topic, but I haven't found anything that works for me. I have a table that stores comments for orders. There are many notes for each order and I want to select the newest comment for each order so I can have a list of all orders with their last update.

Example Table (orders):
Order Comment# Note
12345 100 Parts on order
98765 300 Shipped today
12345 200 Sent UPS blue
98765 900 Received parts

Desired Output:
Order Comment# Note
12345 200 Sent UPS blue
98765 900 Received parts

Thank you for your help.

Recommended Answers

All 3 Replies

If you do not store a timestamp with the data, then there is no way to order the output by time, and thus no way to select the most recent row.

Or, I guess you don't really need a timestamp: You could use a 'cookie' that has these properties:
* guaranteed not null
* guaranteed unique (or if non-unique, then you may get any of the set)
* guaranteed ascending (or descending)
Timestamp does offer this assuming the unique part. So would integer not null auto_increment

Member Avatar for rajarajan2017

Use TimeStamp to identify the recent updations.

select order,comment_no, note from orders a inner join (select order, max(comment_no) comment_no from orders GROUP BY order) b
on order=b.order and a.comment_no=b.comment_no
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.