MySQL 5.6 subquery ORDER BY behaviour - fixed

Gillian from Oracle informed me that my query is not valid SQL and the 5.5 version worked just because I was lucky.

The correct way of using aggregate function count() is something like this:

SELECT mlh.changedate, mlh_latest.counts, mlh.level
FROM memberlevelhistory mlh
INNER JOIN (
    SELECT member, MAX(changedate) as maxdate, COUNT(changedate) as counts
    FROM memberlevelhistory
    WHERE member = 5
    AND approved <> 'N'
) AS mlh_latest ON mlh.member = mlh_latest.member AND mlh.changedate = mlh_latest.maxdate
WHERE mlh.member = 5
AND mlh.approved <> 'N';

Now the result is equally correct on both tested versions.