The table in chronological order records the states of users (and their changes), for example,

datetime | uid | status 2016-07-01 | 14 | Register 2016-07-01 | 14 | Active 2016-07-02 | 15 | Active 2016-07-02 | 16 | Register 2016-07-02 | 14 | Pending 2016-07-04 | 16 | Pending

How to formulate a MySQL query correctly in order to get

 datetime | uid | status 2016-07-02 | 15 | Active 2016-07-02 | 14 | Pending 2016-07-02 | 16 | Pending

That is, how is it possible in the MySQL query to indicate that only the most recent states for the unique uids are output? I tried GROUP BY , DISTINCT and MAX() , but the time displays exactly the maximum, and the status value is not the maximum.

PS The table itself gave a schematic, the time is fixed up to a second, there are no intersections in time. Just the last entry for each of the uid is needed. I would be grateful for at least a hint in which direction to look.

  • First select distinct uid , then inner join with the same table to get the date and status. - nzeemin
  • 2
    Here's a popular question with detailed explanations of stackoverflow.com/questions/7745609/… - cache
  • @cache, thanks! Excellent link helped. It did not get on the search for some reason. Unexpected solution with WHERE b.id IS NULL, but logical. - ilyinilyas

3 answers 3

 select uid, datetime, status from tablename join ( select uid, max(`datetime`) as datetime from tablename group by uid ) lastvalues using(uid, datetime)

Under the condition of the uniqueness (or better, the unique index), the uid & datetime pair will return the correct result.

  • Thank. And what is the construction of lastvalues? Never met. Is it LAST VALUES? I am trying to apply your options from the link now. - ilyinilyas
  • one
    lastvalues ​​is an alias. Instead of it (lastvalues) you can enter any identifier name. For example a or t - nick_n_a
 SELECT uid, max(`datetime`) as datetime, substr(max(concat(`datetime`,status)),20) as status FROM tablename GROUP BY uid

The offset 20 in substr is indicated on the assumption that the datetime field has a datetime data type. For other data types, you must specify a suitable offset based on the length of the date field in the symbolic representation. Also make sure that with your regional settings, when you automatically convert the date to the string, the components go in the order year-month-day, for proper sorting.

    It seems so:

     SELECT max(`datetime`) as datetime, uid, status FROM tablename GROUP BY uid

    I can not guarantee that it works as it was intended (on the test sample it gave an ok result), third-party confirmation / rebuttal is needed

    • 2
      In the default mysql 5.7 query will not start at all and will return an error about accessing the non-aggregated field. In older versions or after modifying sql_mode - datetime, yes, there will be the last one, but status - any. - Fine
    • @ Small oh, no wonder I hate him, so - etki