Solved

MySQL ordering results before grouping

Posted on 2014-12-09
2
198 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

837 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