Solved

Getting latest record in each group in MySQL

Posted on 2014-01-30
4
283 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
  • 2
  • 2
4 Comments
 
LVL 24

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 24

Accepted Solution

by:
chaau earned 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

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). …
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now