I have this SQL table called messages which has the details like following;
id, sender, recipient, message, timestamp
"1","10204456572654160","10208819391147662","Hi.. : What are you up to?","1459541723279"
"2","10204456572654160","10208819391147662","Got my test message?","1459541749818"
"3","10208819391147662","10204456572654160","This is my message","1459611679108"
"4","10208819391147662","10204456572654160","And another message","1459611735455"
"5","10204456572654160","10208819391147662","And I reply like this","1459611758570"
"6","10153775515332771","10208819391147662","It's me.. Syco !!","1459611900348"
"7","10153775515332771","10208819391147662","You there?","1459611900350"
"8","10208819391147662","10153775515332771","Yes.. What's upp..","1459611900380"
"9","10204464403169266","10208819391147662","How're you doin?","1459612000666"
Now, I want to pass a value to my function e.g. 10208819391147662 and to show result like following columns;
- Unique value of
sender/recipientwhich is NOT10208819391147662 - The last
messagebetweensender/recipientwhich is NOT10208819391147662ANDsender/recipientwhich is10208819391147662. - The
timestampof the 2nd (message).
In order to achieve the following, I have this sql;
SELECT s1.*, max(c2.message), max(c2.timestamp)
FROM (
SELECT sender as username
FROM messages
WHERE sender <> '10208819391147662'
UNION
SELECT recipient as username
FROM messages
WHERE recipient <> '10208819391147662'
) s1
LEFT JOIN messages c2 ON (s1.username = c2.sender OR s1.username = c2.recipient)
GROUP BY s1.username
I have sucessfully extracted the 1st column by using UNION. But with no lucks on the 2nd and 3rd by using MAX and Group by clause.
My final expected result should be;
username, message, timestamp
"10204456572654160","And I reply like this","1459611758570"
"10153775515332771","Yes.. What's upp..","1459611900380"
"10204464403169266","How're you doin?","1459612000666"
And here's my SQLFiddle so far. Any suggestion is appreciated.
Aucun commentaire:
Enregistrer un commentaire