issue with getting the last inner join record base on datetime

Question

I'm trying to get the last datetime record from a table that happens to store multiple status. My table looks like so:

 +---------+--------------------+-------+-------+
 |status_id|status_timestamp    |User id|Status |
 +---------+--------------------+-------+-------+
 |1        |2012-02-14 12:04:45 |2      |active |
 |2        |2012-02-14 12:14:20 |2      |inactive| <-want that value only
 |3        |2013-02-14 12:20:59 |3      |desact |
 +---------+--------------------+-------+-------+

 SELECT st.Status, st.status_timestamp
 FROM status_table as st
 INNER JOIN user_profile as up ON st.user_id = up.user_id
 WHERE up.username = "usertest";

usertest is user_id 2

I try using max(st.status_timestamp) but i could not get it to work

neither are those 2 questions was i aware exist when i did my research but if you want to delete it, fine by me. — John Laimuin, Aug 13 '17 at 14:32

score 0 · Accepted Answer · answered Aug 13 '17 at 14:20

One method uses a correlated subquery in the WHERE clause:

SELECT st.Status, st.status_timestamp
FROM status_table st INNER JOIN
     user_profile up
     ON st.user_id = up.user_id
WHERE up.username = 'usertest' AND
      st.status_timestamp = (SELECT MAX(st2.status_timestamp)
                             FROM status_table st2
                             WHERE st2.user_id = st.user_id
                            );

If you are only expecting one row back, then a simpler method is ORDER BY and LIMIT:

SELECT st.Status, st.status_timestamp
FROM status_table st INNER JOIN
     user_profile up
     ON st.user_id = up.user_id
WHERE up.username = 'usertest'
ORDER BY st.status_timestamp DESC
LIMIT 1;

does the first method may give me multiple values if it was post at the same time? — John Laimuin, Aug 13 '17 at 14:26
@JohnLaimuin . . . Yes, up to the precision of the timestamp column that you are using. — Gordon Linoff, Aug 13 '17 at 16:23

issue with getting the last inner join record base on datetime

1 Answers1