SELECT grouping problem with MAX()

[Lyle Hopkins]

Hi All,
I've been ripping my hair out trying to get this select to return what I want. Here is what I have so far:-

create table paylist (date int(8), username varchar(12), amount real(12,2))

Some example data:-
INSERT into paylist(date, amount, username) values ('20080101', 10, 'bob'), ('20080102', 20, 'bob'), ('20080103', 5, 'bob'), ('20080102, 0, 'laura')

What I want is to get returned is this:-
20080103, 5, bob
20080102, 0, laura

The real table has lots of different users. Now if I use:-

SELECT MAX( date ) AS m, amount, username
FROM `paylist`
WHERE 1
GROUP BY username
ORDER BY m DESC

I Get:-
20080103, 10, bob
20080102, 0, laura

Seems no matter what I try, I cannot get the amount that corrosponds with the MAX( date ).

Please someone help me.


Thank you.