reciver-id | sender-id | date | text 1 | 44 | 21.12.2011 | qwerty 1 | 2 | 23.12.2011 | qwerty 1 | 3 | 21.12.2011 | qwerty 1 | 2 | 15.12.2011 | qwerty 1 | 44 | 20.12.2011 | qwerty

There is such a table of messages. How can I make a request for mysql to pull out only the last message from each user who sent messages?

From this table you should get this array:

reciver-id | sender-id | date | text 1 | 44 | 21.12.2011 | qwerty 1 | 2 | 23.12.2011 | qwerty 1 | 3 | 21.12.2011 | qwerty

  • Does it make sense? Why do we need information about the last message of each user? I just can not imagine why, and I'm interested. - Artem
  • It is elementary to add a message_id column to the table with an auto-complete (Auto_increment!), And when grouping records, pull out the largest message_id from each user - oxyage
  • and how to implement it? - ola_sh 2:49 pm

4 answers 4

If you follow the table structure specified in the question, then:

 SELECT * FROM table_name t1 INNER JOIN ( -- Подзапрос группирует отправителей и выводит дату последнего сообщения SELECT `sender-id`, MAX(`date`) AS `date` FROM table_name GROUP BY 1 ) t2 ON (t2.`sender-id` = t1.`sender-id`) AND (t2.`date` = t1.`date`)

It is only necessary to take into account that if the type of the column is DATE, then several messages can be sent to the same day by the same user. In this case, the query will output all several. In your place in the message table, I would add an auto-increment key. It would be a guarantee of uniqueness for each message, and it could be relied upon when selecting the latter.

     SELECT MAX(Date) AS d,Sender_ID INTO #Temp FROM TABLE GROUP BY Sender_ID SELECT * FROM TABLE _t INNER JOIN #Temp t ON td=_t.Date AND t.Sender_ID = _t.Sender_ID DROP TABLE #Temp
    • In MySQL, INTO used at the end of a query, and only to transfer a single value to variables. To create a temporary table based on a query, use CREATE TEMPORARY TABLE tmp_table ENGINE = <engine> AS SELECT * FROM table_name . - KiTE
    • awful I will take note - den94
     reciver-id | sender-id | display | date | text

    Add to the display example and when adding a new message - all messages with the value 1 make an update to 0 and write a new message to 1. and, accordingly, the WHERE display > 0 request

    To add is ALTER TABLE *table* ALTER COLUMN *column* - just in case)

    • Honestly, there is such a field, called status, whether the message is read or not. - ola_sh
    • new! = read =) - Gorets
     select sender-id, max(date) from table group by sender-id

    I feel that I was mistaken somewhere, but the meaning is this =)

    • Aggregate functions cannot be used in WHERE - KiTE
    • and so?) for a long time already did not work with the database =) - Gorets