[thelist] SQL - Correlated Subquery??
Luther, Ron
Ron.Luther at COMPAQ.com
Wed Dec 20 09:06:52 CST 2000
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 :)
More information about the thelist
mailing list