Solved

Getting latest record in each group in MySQL

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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