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.
