kittykotty kittykotty - 5 months ago 19
MySQL Question

mysql order by followed by group by

so i'm about to migrate my server to a new machine. i install the newer version of mysql 5.7.13 on the new machine while the old one mysql 5.5.13. and there is this one query that doesn't work like it works before
with the same data as previous server

select *
from
( select *
from user_config
where username like '%'
and updatetime <= '2500-01-01 00:00:00'
order
by updatetime desc
) x
group
by username;


the mysql 5.5.13 works as i want, it load the newest configuration setting of every user. but the 5.7.13 instead load the oldest configuration setting of every user

why is this happening?

i tried to run the subquery and it works both fine but when i combine to the query above it doesn't work

example data

username|value|updatetime
a | 100|2014-01-01 00:00:00
b | 85|2014-06-01 00:00:00
a | 150|2016-05-01 00:00:00
c | 50|2016-06-01 00:00:00
c | 75|2016-06-23 00:00:00


result i need is

a|150
b|85
c|75

Answer
SELECT x.* 
  FROM my_table
  JOIN 
     ( SELECT username
            , MAX(updatetime) updatetime
         FROM my_table
        GROUP 
           BY username
     ) y
    ON y.username = x.username
   AND y.updatetime = x.updatetime;