[thelist] SQL - Correlated Subquery??
Joshua OIson
joshua at alphashop.net
Tue Dec 19 18:13:31 CST 2000
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 !
More information about the thelist
mailing list