GROUP BY LAST DATE MYSQL

Question

I have this tables,

user
  id
  name

visit
  id
  id_user (fk user.id)
  date
  comment

If i execute this query,

SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id

I get,

1 Jhon 1 2013-12-01 '1st Comment' 
1 Jhon 2 2013-12-03 '2nd Comment' 
1 Jhon 3 2013-12-01 '3rd Comment'

If I GROUP BY u.id, then I get

1 Jhon 1 2013-12-01 '1st Comment'

I need the last visit from Jhon

1 Jhon 3 2013-12-04 '3rd Comment'

I try this

SELECT u.id, u.name, e.id, MAX(e.date), e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
GROUP BY u.id

And this,

SELECT u.id, u.name, e.id, MAX(e.date), e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
GROUP BY u.id 
HAVING MAX(e.date)

And I get

1 Jhon 1 2013-12-04 '1st Comment'

But this is not valid to me... I need the last visit from this user

1 Jhon 3 2013-12-01 '3rd Comment'

Thanks!

score 2 · Accepted Answer · answered Dec 04 '13 at 13:58

2

This should give you the last comment for every user:

SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN (SELECT t1.*
  FROM visit t1
    LEFT JOIN visit t2
      ON t1.id_user = t2.id_user AND t1.date < t2.date
  WHERE t2.id_user IS NULL
  ) e ON e.id_user=u.id

answered Dec 04 '13 at 13:58

Filipe Silva

21,189
5
53
68

Thanks! For visits to the same date I added ORDER BY DESC t1.id DESC – Ricmcm Dec 04 '13 at 17:17

score 1 · Answer 2 · answered Dec 04 '13 at 13:54

1

SELECT u.id, u.name, e.id, e.date, e.comment
FROM user u
LEFT JOIN visit e ON e.id_user=u.id
ORDER BY e.date desc
LIMIT 1;

answered Dec 04 '13 at 13:54

Tom Mac

9,693
3
25
35

1

If he only wants the results for one user this is ideal, but if he wants it for more than one (which i believe he does), this will not work – Filipe Silva Dec 04 '13 at 14:02
Fair point. Was just going on my interpretation of the OP. Specifically "But this is not valid to me... I need the last visit from this user". So I guessed there would be a `where` clause in there somewhere... – Tom Mac Dec 04 '13 at 14:14

score 0 · Answer 3 · answered Dec 04 '13 at 13:55

0

use:

SELECT u.id, u.name, e.id, e.date, e.comment FROM user u LEFT JOIN visit e ON e.id_user=u.id order by e.date DESC limit 0,1

Will show 1 record order by date.

Hope will help!

answered Dec 04 '13 at 13:55

Rajiv Ranjan

1,869
1
11
20

GROUP BY LAST DATE MYSQL

3 Answers3