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