• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Getting latest record in each group in MySQL

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
ivanblue
Asked:
ivanblue
  • 2
  • 2
1 Solution
 
chaauCommented:
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
 
ivanblueAuthor Commented:
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
 
chaauCommented:
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
 
ivanblueAuthor Commented:
Works perfect Chaau!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now