Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Getting latest record in each group in MySQL

Posted on 2014-01-30
4
Medium Priority
?
316 Views
Last Modified: 2014-01-31
Hello, I need to make a query that returns the latest messages in a communication between two people. This my structure:

UsrCodPoster int(11) NOT NULL,
UsrCodReceiver int(11) NOT NULL,
UPMCod int(11) NOT NULL AUTO_INCREMENT,
UPMDatetime datetime DEFAULT NULL,
UPMMessage text

Open in new window



UsrCodPoster	UsrCodReceiver	UPMCod	UPMDatetime		UPMMessage
3		7		4	29/01/2014 03:00	Response 2
3		7		3	29/01/2014 01:00	response 1
7		3		1	27/01/2014 23:35	Test message 1
7		3		2	27/01/2014 23:39	Mensaje de prueba 2
5		7		5	30/01/2014 01:01	Pregunta 1
7		5		6	30/01/2014 22:03	Respuesta 1
5		7		7	30/01/2014 22:03	pregunta 2
7		5		8	30/01/2014 22:04	respuesta 2

Open in new window


There should be two groups, the 3 with the 7 and the 5 with 7, showing this result:

UsrCodPoster	UsrCodReceiver	UPMCod	UPMDatetime		UPMMessage
3		7		4	29/01/2014 03:00	Response 2
7		5		8	30/01/2014 22:04	respuesta 2

Open in new window


This code got me close but is not showing the latest message:
SELECT 
	user_private_message.UsrCodPoster,
	user_private_message.UsrCodReceiver,
	user_private_message.UPMCod,
	user_private_message.UPMDatetime,
	user_private_message.UPMMessage
FROM
	user_private_message
WHERE
	(user_private_message.UsrCodPoster = 7 OR
	user_private_message.UsrCodReceiver = 7)
GROUP BY
	(CASE WHEN (user_private_message.UsrCodPoster = 7) THEN
    	user_private_message.UsrCodReceiver
	ELSE
		user_private_message.UsrCodPoster
    END)

Open in new window


How can I do this? Thanks in advance!
0
Comment
Question by:ivanblue
[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
  • 2
4 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39823090
You usually do this by joining to the same table grouped by the MAX() value, like this:
SELECT 
	user_private_message.UsrCodPoster,
	user_private_message.UsrCodReceiver,
	user_private_message.UPMCod,
	user_private_message.UPMDatetime,
	user_private_message.UPMMessage
FROM
	user_private_message INNER JOIN
        (SELECT 	UsrCodPoster,
	UsrCodReceiver,
	MAX(UPMDatetime) AS _maxUPM
        FROM user_private_message GROUP BY UsrCodPoster,
	UsrCodReceiver) u ON 
       	user_private_message.UsrCodPoster = u.UsrCodPoster AND
   	user_private_message.UsrCodReceiver = u.UsrCodReceiver AND
        user_private_message.UPMDatetime = u._maxUPM
WHERE
	(user_private_message.UsrCodPoster = 7 OR
	user_private_message.UsrCodReceiver = 7)

Open in new window

0
 
LVL 1

Author Comment

by:ivanblue
ID: 39823755
Hello Chaau, trying your code I get this result:


UsrCodPoster	UsrCodReceiver	UPMCod	UPMDatetime		UPMMessage
3		7		4	29/01/2014 03:00	Response 2
5		7		7	30/01/2014 22:03	pregunta 2
7		3		2	27/01/2014 23:39	Mensaje de prueba 2
7		5		8	30/01/2014 22:04	respuesta 2

Open in new window


When it should be:
UsrCodPoster	UsrCodReceiver	UPMCod	UPMDatetime		UPMMessage
3		7		4	29/01/2014 03:00	Response 2
7		5		8	30/01/2014 22:04	respuesta 2

Open in new window


because the records for UsrCodPoster, UsrCodReceiver 5 and 7, and 7 and 5 as well as UsrCodPoster, UsrCodReceiver 3 and 7, and 7 and 3 should be groupped together forming just two groups.
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39823832
OK, I get it. Modify the statement like this:
SELECT 
	user_private_message.UsrCodPoster,
	user_private_message.UsrCodReceiver,
	user_private_message.UPMCod,
	user_private_message.UPMDatetime,
	user_private_message.UPMMessage
FROM
	user_private_message INNER JOIN
        (SELECT
 	CASE WHEN UsrCodPoster > UsrCodReceiver THEN UsrCodPoster ELSE UsrCodReceiver END AS BigUserCode,
 	CASE WHEN UsrCodPoster > UsrCodReceiver THEN UsrCodReceiver ELSE UsrCodPoster END AS SmlUserCode,
	MAX(UPMDatetime) AS _maxUPM
        FROM user_private_message GROUP BY
  	CASE WHEN UsrCodPoster > UsrCodReceiver THEN UsrCodPoster ELSE UsrCodReceiver END,
 	CASE WHEN UsrCodPoster > UsrCodReceiver THEN UsrCodReceiver ELSE UsrCodPoster END) u ON 
       	CASE WHEN user_private_message.UsrCodPoster > user_private_message.UsrCodReceiver THEN user_private_message.UsrCodPoster ELSE user_private_message.UsrCodReceiver END = u.BigUserCode AND
       	CASE WHEN user_private_message.UsrCodPoster > user_private_message.UsrCodReceiver THEN user_private_message.UsrCodReceiver ELSE user_private_message.UsrCodPoster END = u.SmlUserCode AND
        user_private_message.UPMDatetime = u._maxUPM
WHERE
	(user_private_message.UsrCodPoster = 7 OR
	user_private_message.UsrCodReceiver = 7)

Open in new window

Looks a bit bulky, but should do the job right
0
 
LVL 1

Author Comment

by:ivanblue
ID: 39823960
Works perfect Chaau!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

636 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