[thelist] SQL - Correlated Subquery??
Oliver Lineham
oliver at lineham.co.nz
Tue Dec 19 18:35:30 CST 2000
At 16:29 19/12/2000 -0600, you wrote:
>I have a table with (at least) three columns;
>
>Region - North America, Europe, Asia
>
>Process completion date - (completed program #6 at) 12/17/00 3:45:00am
>
>Data "as of" date - (data for) 12/16/00 12:00:00am
>
>What I'd really really like is to return 3 records - one showing me the
>maximum "as of' date for each region.
>
>This should be easy, I'm hanging my head, I'm just not getting it!
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 :)
i haven't seen the post you were replying to (it hasn't come in yet for
me), but i assume they were suggested a nested SELECT statement. these are
usually less efficient, so if you can find another way - use it.
</ol>
____________________________________________________
v i b e m e d i a http://www.vibe.co.nz/
po box 10-492 wellington, new zealand
phone +64 21 210-7845 oliver at lineham.co.nz
More information about the thelist
mailing list