CODEWITHSUNDEEP
mysqlsql
alex rees
alex rees

Reputation: 79

Select from two tables but only for the maximum values in one of the columns

I have the following two tables. I want to select all the users with user_type 1, as well as the details for their most recent note. I want to order the results by the note time_created. 

users table:
user_id   name   user_type
1         Joe    1
2         Fred   1
3         Sam    0
4         Dave   0

notes table:
notes_id  user_id note            time_created
1         1       Joe 1           2019-06-05 13:45:00
2         1       Joe 2           2019-06-05 13:46:00
2         1       Joe 3           2019-06-05 13:47:00
3         2       Fred 1          2019-06-05 13:45:00
4         2       Fred 2          2019-06-05 13:46:00
5         3       Sam 1           2019-06-05 13:45:00
6         4       Dave 1          2019-06-05 13:45:00

So the result of the query would be 

user_id name   note     time_created
1       Joe    Joe 3    2019-06-05 13:47:00
2       Fred   Fred 2   2019-06-05 13:45:00

This is my effort so far

SELECT users.user_id, users.name, notes.note, notes.time_created FROM notes 
INNER JOIN users ON users.id=prospect_notes.subject_id  
WHERE users.user_type = 1 ORDER BY notes_time_created;

It is pretty good, but it returns several rows per user. I just want the one row containing the maximum time created.

Upvotes: 0

Views: 19

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271211

You can use a correlated subquery:

SELECT u.user_id, u.name, n.note, n.time_created
FROM users u JOIN
     notes n
     ON u.id = n.subject_id  
WHERE u.user_type = 1 AND
      n.notes_time_created = (SELECT MAX( n2.notes_time_created )
                              FROM notes n2
                              WHERE n2.subject_id = n.subject_id
                             )
ORDER BY n.notes_time_created;

Upvotes: 1

Related Questions