MySQL issues with query

[jnymris]

Firstly, Sorry about the lack of detail in the title, i'm not sure how to describe what i'm trying to achieve without going into detail.

 

So I have a table:

mysql> SELECT * FROM notes;
+----+---------+-----------------+---------------------+----------+
| id | user_id | note            | datetime            | order_id |
+----+---------+-----------------+---------------------+----------+
|  1 |       7 | Update Please?  | 2018-02-16 22:22:18 |        1 |
|  2 |       6 | Update Provided | 2018-02-16 22:25:18 |        1 |
|  3 |       7 | test            | 2018-02-17 10:03:20 |        2 |
+----+---------+-----------------+---------------------+----------+

 

I'm trying to get the most recent row for each order_id. from the data above it result would be: Rows 2 and 3.

I thought using a subquery would be the best way to go but i'm getting unexpected result:

If i run the first part of the query I do get ID row 2 and 3 (which is a good sign).

SELECT DISTINCT (order_id) FROM notes;

If i run the second part of the query: I do get one result.

SELECT user_id FROM notes WHERE order_id=1 ORDER BY datetime DESC LIMIT 1;

 

When I combine both queries together I get an odd result.

SELECT DISTINCT (order_id), user_id = (SELECT user_id FROM notes tbl2 WHERE tbl2.order_id=tbl.order_id ORDER BY datetime DESC LIMIT 1) FROM notes tbl;

RESULT:

+----------+------------------------------------------------------------------------------------------------------------+
| order_id | user_id = (SELECT user_id FROM notes tbl2 WHERE tbl2.order_id=tbl.order_id ORDER BY datetime DESC LIMIT 1) |
+----------+------------------------------------------------------------------------------------------------------------+
|        1 |                                                                                                          0 |
|        1 |                                                                                                          1 |
|        2 |                                                                                                          1 |
+----------+------------------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

 

Could anyone point me where i am going wrong?

 

If it helps i'm actually only interested in getting the last row matches a specific value (7), but i just planned on doing this match with PHP

if($user_id==7)

//echo Will Display Data;

[justsomeguy]

SELECT MAX(id), order_id FROM table GROUP BY order_id

That will get the largest ID for each order.  You can use that in a subquery to get the rest of the data for each row.