Jump to content
Sign in to follow this  
jnymris

MySQL issues with query

Recommended Posts

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;

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×