0

I have the following table in MySQL with an auto-increment ID field and an auto timestamp field(not mentioned here). I am trying to group by the rows on order# and get the latest row.

Order Table -

id order# status
1 100 pending
2 100 processing
3 100 delivered
4 101 pending
5 101 processing
6 101 cancelled

Expected Answer -

id order# status
3 100 delivered
6 101 cancelled

So far this is all I could come up with. I spend few hours trying to google a solution, but couldnt find something clear.

SELECT * FROM TABLE ORDER BY order# , id DESC;

Mihir
  • 43
  • 6
  • instead of ordering by the id, how about using the timestamps? Don't you have an updated_at column? – IGP Jan 13 '21 at 01:36
  • I do have an updated_at, but I need the latest row for every order#. I was trying id, because its autoincremented and would always be higher than the previous entries. (not sure if its safe) – Mihir Jan 13 '21 at 01:37
  • It should be if that's what you're looking for. – IGP Jan 13 '21 at 01:44
  • that doesnt give me the first row though, it will give me all the rows ordered in that manner by updated_at – Mihir Jan 13 '21 at 01:47
  • This has already been answered in https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql/6572292 . Just use it as a subquery. `SELECT * FROM (SELECT * FROM table ORDER BY ...) A GROUP BY id` – IGP Jan 13 '21 at 02:06
  • Does this answer your question? [ORDER BY date and time BEFORE GROUP BY name in mysql](https://stackoverflow.com/questions/6572110/order-by-date-and-time-before-group-by-name-in-mysql) – IGP Jan 13 '21 at 02:07

0 Answers0