There is a table of messages , which has the following form: _id (prim key), author, client, time, content.

Let it have the following meanings:

  _id | author | client | time | content -------------------------------------- 1 | user | you | 21:52 | hi 2 | user | you | 20:43 | wassup 3 | user2 | you | 17:45 | привет 4 | user2 | you | 18:20 | :(

From this table you need to get one (last) message from each author, where client = you .

Those. using the request, you need to return messages with id 1 and 4 ( hi and :( ). How can this be done?

    2 answers 2

    Indeed there is an error:

     SELECT * FROM `table` WHERE `client` = 'you' GROUP BY `author` ORDER BY `time` DESC;

    Sorting occurs after the grouping, you need such a query:

     SELECT `message`.* FROM `message` INNER JOIN ( SELECT max(`time`) AS `maxTime`, `author` FROM `message` GROUP BY `author` ) AS `max` ON (`max`.`author` = `message`.`author` // При возможности связать по ID AND `max`.`maxTime` = `message`.`time` AND `client` = 'you' ) ORDER BY `time` DESC;
    • one
      Thank you, everything works, only ORDER BY should be after GROUP BY - lounah
    • @Schepalin on my example in sqlfiddle it will display messages with id 1 and 3, is that really true? - Denis
    • one
      @Schepalin Firstly, you may have serious problems when changing the version of MySQL because all the columns that are present in the selection list without group functions should be listed in group by (and here we generally have *). Secondly, ORDER BY works already after group by and if suddenly on some kind of database such a query produces the desired result, then this is a matter of luck, not the correct query. such a group by in MySQL will return the first line that occurred and not with the maximum date - Mike
    • one
      In your subquery after INNER JOIN will be an error with other data in the database - if there is a client not you and it has max (time), then another message will be received. It is also necessary to add a subquery with the client tuple. - Denis

    sqlfiddle

     SELECT t1.* FROM test t1 INNER JOIN (SELECT author, client, max(time) as mtime FROM test WHERE client = 'you' GROUP BY author, client) t2 ON t1.author = t2.author AND t1.client = t2.client AND t1.time = t2.mtime
    • @Mike do not tell me, in MySQL there is an analogue keen (dense_rank first order by ...) as in Oracle? It would be more beautiful to make a decision. - Denis
    • There are no window functions in MySQL. cumulative results can only be obtained using variables and "manual grouping". something like select * from ( select *,@num:=if(author=@grp, @num+1, 0) N,@grp:=author from test, (select @grp:='', @num:=0) A order by author, time ) A where N=0 will be released - Mike