Justin Greenberg Justin Greenberg - 20 days ago 7
MySQL Question

Using Distinct(col) in a SELECT with more than one column selected

my goal is to return a distinct list of 'mFrom' and only pull the latest 'date' from 'messages' table..

Table: messages
id mFrom mTo date
-- ----- --- ----
int int int datetime


I am trying to use this query:

SELECT DISTINCT(mFrom), date FROM messages WHERE mTo = '116'


But am receiving these results:

mFrom date
9 | 2016-11-17 00:30:03
11 | 2016-11-17 12:35:08
11 | 2016-11-17 12:35:35


and I would like to see these results instead..

mFrom date
9 | 2016-11-17 00:30:03
11 | 2016-11-17 12:35:35


Any help is appreciated. Saw similar answers using GROUP BY and an inner SELECT but having trouble coming up with a comparable query for my specific fields..

Answer

GROUP BY seems right.

SELECT mFrom, MAX(date) as date 
FROM messages 
WHERE mTo = '116' 
GROUP BY mFrom