Show the ten first contacts that I have recieved message
I want to create Sql statement that show the ten first contacts that I have recieved message from along with their latest sent message and time. The table columns is messageId, messageBody, fromUser, toUser, timeStamp
and table is called messages
. The data开发者_C百科base is Mysql and Java is the language. But I want this to happen in one single sql statement.
I'm interpreting "show the ten first contacts" as the first 10 people that submitted messages.
Select M2.fromUser
, LastMessage.messageBody
, LastMessage.timeStamp
From (
Select M1.fromUser
, Min(M1.timeStamp) As FirstMessage
, Max(M1.messageId) As LastMessageId
From messages As M1
Where M1.toUser = 'my username'
Group By M1.fromUser
Order By Min(M1.timeStamp) Asc
Limit 10
) As M2
Join messages As LastMessage
On LastMessage.messageId = M2.LastMessageId
Something like this might work:
SELECT fromUser, messageBody, timeStamp
FROM (SELECT DISTINCT fromUser FROM messages ORDER BY timeStamp LIMIT 10) AS m1
LEFT JOIN messages
ON(fromUser)
ORDER BY timeStamp DESC LIMIT 1
You could use group by
to find the latest message per contact. Then you can join back to the message table to retrieve the message body:
select LastTen.Contact
, LastTen.LastMessageDate
, msg.messageBody
from (
select fromUser as Contact
, max(timeStamp) as LastMessageDate
from YourTable
where toUser = 'YourName'
group by
fromUser
order by
max(timeStamp) desc
limit 10
) LastTen
join YourTable msg
on msg.fromUser = lastTen.Contact
msg.timeStamp = lastTen.LastMessageDate
SELECT fromUser, timeStamp, messageBody FROM messages ORDER BY timeStamp LIMIT 10
精彩评论