[thelist] SQL - Correlated Subquery??

[Joshua OIson]

something like this might work:

    SELECT
      DISTINCT b.region,
      (SELECT Max(as_of_date) FROM mytable a
       WHERE a.region = b.region) as max_as_of_date
    FROM mytable b

Basically, the "distinct" keyword ensures that you get one row per region,
and the subselect calculates the maximum date, given a constraint that your
only looking within a particular region.

-joshua

----- Original Message -----
From: "Luther, Ron" <Ron.Luther at COMPAQ.com>
To: <thelist at lists.evolt.org>
Sent: Tuesday, December 19, 2000 2:29 PM
Subject: [thelist] SQL - Correlated Subquery??


> Hi Gang,
>
>
> 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
>
> (The table has - oh - let's say 300,000 records in it.  It is a
"processing
> history" table.)
>
>
>
> 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!
>
>
>
> TIA,
>
> Ron L.
>
>
> ---------------------------------------
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !