• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

MySQL ordering results before grouping

Hi,

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 ;

Open in new window


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

Open in new window


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?
0
SheppardDigital
Asked:
SheppardDigital
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may want to read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html

for MySQL, the solution is a subquery :
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)
AND a.date = ( select max(x.date) from activity as x where x.id = u.user_id                                  

Open in new window

0
 
SheppardDigitalAuthor Commented:
It looks like that worked perfectly. The final query was;

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) 
AND a.date = (SELECT MAX(x.date) FROM activity AS x WHERE x.user_id = a.user_id)
GROUP BY a.user_id

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now