0

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.

4
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by urtrivedi
0

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

Edited by griswolf: n/a

0
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

Edited by urtrivedi: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.