I have the following table in a database;
CREATE TABLE IF NOT EXISTS `activity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`mood_id` int(11) NOT NULL,
`date` datetime NOT NULL,
`lat` varchar(30) NOT NULL,
`lon` varchar(30) NOT NULL,
`latlng` geometry NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
I'm running the following query on the table.
SELECT a.*,u.sex FROM activity AS a, users AS u WHERE u.id = a.user_id AND a.id > 0 AND a.lat != '' AND a.lon != ''AND MBRContains(GeomFromText('LineString(52.74128 -0.878906, 52.349471 0.152435)'), a.latlng)GROUP BY a.user_id
In general the query works fine in general, however there can be multiple entries in the table with the same user_id. So what I would like to do is sort the results by date so the latest entries are first, and then apply the group by a.user_id so that the query only returns the most recent entry in the table for each user.
I remember coming across this issue in the past and I believe I had to perform a sub query first to sort by date, and then group, but I can't remember.
Can someone help please?