Solved

MySQL ordering results before grouping

Posted on 2014-12-09
2
199 Views
Last Modified: 2014-12-09
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
Comment
Question by:SheppardDigital
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40489298
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
 

Author Comment

by:SheppardDigital
ID: 40489669
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question