Desmond Desmond - 9 days ago 6
MySQL Question

How to get latest results by date when selecting from two table?

I have two tables and I would like to join then with a query.

result
save the actual entry of results

user_tracking
tracks the acceptance and completion of work, users can cancel and accepts work again at a later time.

SELECT *
from
svr1.result r,
svr1.user_tracking u
where
r.uid = u.user_id and r.tid = u.post1
and u.function_name = '7' #7 == accept work
and r.insert_time > '2015-09-23 00:00:00' and r.insert_time < '2015-10-03 00:00:00'
and u.track_time > '2015-09-23 00:00:00' and u.track_time < '2015-10-03 00:00:00'


my
result
table had 1785 records within the period I wanted to track
but the above query returns 1990 records. I would like to know how can i filter to get the latest date accepted by user only.

in
result
table: uid,INT, tid,INT, result,VARCHAR and insert_time,TIMESTAMP

in
user_tracking
table: user_id,INT, post1,VARCHAR function_name,VARCHAR, result,VARCHAR and track_time,TIMESTAMP

the user_tracking function sample records, in this query the track time will change and the rest will remain the same.

enter image description here

AER AER
Answer

Use the GROUP BY command with a MAX() on the required date, this will select the latest date of all the options (assuming all the other columns are equal). Code as follows (need to declare all columns because of the MAX unfortunately):

SELECT r.uid,
    r.tid,
    r.result,
    r.insert_time,
    u.user_id,
    u.post1,
    u.function_name,
    u.result,
    MAX(track_time)        
FROM
svr1.result r, 
svr1.user_tracking u 
WHERE
r.uid = u.user_id AND r.tid = u.post1
AND u.function_name = '7' #7 == accept work
AND r.insert_time > '2015-09-23 00:00:00' AND r.insert_time < '2015-10-03 00:00:00' 
AND u.track_time > '2015-09-23 00:00:00' AND u.track_time < '2015-10-03 00:00:00'
GROUP BY
    r.uid,
    r.tid,
    r.result,
    r.insert_time,
    u.user_id,
    u.post1,
    u.function_name,
    u.result