Solved

MySQL ordering results before grouping

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

737 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