List:General Discussion« Previous MessageNext Message »
From:Robin Bowes Date:April 29 1999 10:35am
Subject:Re: Select most recent row - another one for the FAQ perhaps?
View as plain text  
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: +44 161 838 8321  Fax: +44 161 835 1657
Thread
Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Christian Mack29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Fraser MacKenzie29 Apr
      • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
        • Select most recent row - another one for the FAQ perhaps?Fraser MacKenzie29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Erik Liljencrantz29 Apr
  • Re: Select most recent row - another one for the FAQ perhaps?Fred Read29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Michael Longval1 May
      • Re: Select most recent row - another one for the FAQ perhaps?Thimble Smith1 May
  • Re: Select most recent row - another one for the FAQ perhaps?Robin Bowes29 Apr
    • Re: Select most recent row - another one for the FAQ perhaps?Erik Liljencrantz29 Apr
      • Re: Select most recent row - another one for the FAQ perhaps?Michael Widenius30 Apr