Solved

Getting latest record in each group in MySQL

Posted on 2014-01-30
4
290 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

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 …
Creating and Managing Databases with phpMyAdmin in cPanel.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
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…

809 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