List:General Discussion« Previous MessageNext Message »
From:Fraser MacKenzie Date:April 29 1999 11:45am
Subject:Re: Select most recent row - another one for the FAQ perhaps?
View as plain text  
Sure you can do this.  I do it all the time.  Here is the select

select MAX(latest) from MyTable where username='MyUsername';

Fraser

On Thu, 29 Apr 1999, Christian Mack wrote:

> Robin Bowes wrote:
> > 
> > Fred Read wrote:
> > >
> > > Robin Bowes wrote:
> > > >
> > > > Fred Read wrote:
> > > > >     "How do I return the most recent row for a given xxx?"
> > > >
> > > > Try
> > > >
> > > >      select
> > > >          UserName, Created, Latest
> > > >      from
> > > >          MyTable
> > > >      where
> > > >          Latest = max(Latest);
> > > >      and
> > > >          UserName = 'MyUserName'
> > >
> > > That couldn't work because Latest is not a column [see table
> > > definition] it is an alias for a calculated value [see SQL].
> > 
> > For "Latest" read "Created"  - I got the field name wrong.
> > 
> > What I meant to write was:
> > 
> >       select
> >           UserName, Created, Created
> >       from
> >           MyTable
> >       where
> >           Created = max(Created);
> >       and
> >           UserName = 'MyUserName'
> > 
> > That should work for you.
> > 
> > R.
> > --
> > Robin Bowes - System Development Manager - Room 405A
> 
> Hi Robin
> 
> This don't work, because MAX() is a group function, which can't be used in WHERE
> clauses (defined by SQL standard).
> 
> Therefore it is impossible to get what Fred wants to get in one query.
> 
> Tschau
> Christian
> 
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread2599@stripped
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
> 
> 

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