[thelist] SQL - Correlated Subquery??

[Luther, Ron]

Hi OL!


Yeah - Thanks! - That's pretty much exactly what ended up working this
morning!

(I have some extra junk in a WHERE clause ... but the basic structure is
just what you proposed!)

SELECT
  Region, MAX(as_of_date)
FROM
  DataTable
WHERE
  Region = "Mars" AND
  Processed_Table = "the_one_I_want" AND
  Status_Code = "Complete"
GROUP BY
  Region


FWIW, The tool I am using here (Brio) to develop this Intranet report has a
$%^$^%^% GUI interface and I couldn't find the obscure combination of
keystrokes and menu commands necessary to get the dang "MAX" function into
the select statement --- which is what led me down the 'nested query
trail'.....

Found it this morning!

Thanks again for the sanity check!


Ron L.

-----Original Message-----
From: Oliver Lineham [mailto:oliver at lineham.co.nz]
Subject: Re: [thelist] SQL - Correlated Subquery??

i might be misunderstanding your problem, but surely this would work:

SELECT
  thetable.region
  MAX(thetable.asofdate) AS maxasof
FROM
  thetable
GROUP BY
  thetable.region

the group by clause should mean you get one row per region and, should make 
the MAX() find the maximum asofdate from that region.

let me know if this works :)