[thelist] SQL - Correlated Subquery??

[Oliver Lineham]

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