Re: group by complications

Wow!  I didn't know you could have a (select ...) as a replacement for a 'from' table/query.  Your SQL worked as-is,
exceptI had to add a 'limit 1' to the first subquery.<br /><br /> Thanks!  I would have never figured that out on my
own!<br/><br /> Mark<br /><br /> chester c young wrote: <blockquote
cite="mid20060214022937.77875.qmail@web54305.mail.yahoo.com"type="cite"><pre wrap="">--- Mark Fenbers <a
class="moz-txt-link-rfc2396E"href="mailto:Mark.Fenbers@noaa.gov"><Mark.Fenbers@noaa.gov></a> wrote:
 
 </pre><blockquote type="cite"><pre wrap="">select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;

The above query works as expected in that is fetches the lid, fs and 
time of the latest observation in the height table (for the 
corresponding lid), but I also want to fetch (i.e., add to the select
list) the corresponding reading (h.obsvalue) which occurs at 
max(h.obstime).  I'm having trouble formulating the correct SQL
syntax 
to pull out the l.lid, l.fs, and the most recent h.obvalue (with or 
without the time that it occurred).

Logistically, I want to do something like this:

select l.lid,l.fs,most_recent(h.obsvalue) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
   </pre></blockquote><pre wrap="">
use your original query as part of the from clause, then add columns to
it through a subquery or a join.  try something like this:

select q1.*,
(select obsvalue from height where lid=q1.lid and obstime=q1.obstime) as obsvalue
from
(select l.lid,l.fs,max(h.obstime) as obstime1 from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs ) q1;


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
<a class="moz-txt-link-freetext" href="http://mail.yahoo.com">http://mail.yahoo.com</a> 

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
 </pre></blockquote>