Solved

MySQL ordering results before grouping

Posted on 2014-12-09
2
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

705 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