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/recipient
which is NOT10208819391147662
- The last
message
betweensender/recipient
which is NOT10208819391147662
ANDsender/recipient
which is10208819391147662
. - The
timestamp
of 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