List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:April 29 1999 11:15am
Subject:Re: Select most recent row - another one for the FAQ perhaps?
View as plain text  
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

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