Re: Select most recent row - another one for the FAQ perhaps?

[Robin Bowes]

Fred Read wrote:
> 
> I'm sure this one has been asked before but I can't find it in the
> archives.
> 
>     "How do I return the most recent row for a given xxx?"
> 
> +-----------------+---------------+------+-----+---------------------+
> | Field           | Type          | Null | Key | Default             |
> +-----------------+---------------+------+-----+---------------------+
> | Created         | datetime      |      |     | 0000-00-00 00:00:00 |
> | UserID          | int(11)       |      | PRI | 0  (auto increment) |
> | UserName        | char(64)      | YES  |     | NULL                |
> +-----------------+---------------+------+-----+---------------------+
> 
> My gut feeling is this *should* work, but it doesn't, it always returns
> "Empty set (0.08 sec)"...
> 
>     select
>         UserName, Created, max(Created) as Latest
>     from
>         MyTable
>     where
>         UserName = 'MyUserName'
>     group by
>         UserName
>     having
>         Created = Latest;
> 
> I should know this but keep coming up blank, your help would be
> most appreciated...

Try

     select
         UserName, Created, Latest
     from
         MyTable
     where
         Latest = max(Latest);
     and
         UserName = 'MyUserName'

R.
-- 
Robin Bowes - System Development Manager - Room 405A
E.O.C., Overseas House, Quay St., Manchester, M3 3HN, UK.
Tel: [phone]  Fax: [phone]