Jump to content

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;

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...