[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:

  MAX(thetable.asofdate) AS maxasof

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.


     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