[thelist] SQL - Correlated Subquery??

[Luther, Ron]

Thanks Joshua,

I will give it a whirl ... but I'm not sure the tool I'm using (Brio) will
allow me to use the 'distict' keyword in the select list ... so I'll try to
make some changes as appropriate!

Thanks!

Ron L.


-----Original Message-----
From: Joshua OIson [mailto:joshua at alphashop.net]
Sent: Tuesday, December 19, 2000 7:52 PM
To: thelist at lists.evolt.org
Subject: Re: [thelist] SQL - Correlated Subquery??


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 !


---------------------------------------
For unsubscribe and other options, including
the Tip Harvester and archive of TheList go to:
http://lists.evolt.org Workers of the Web, evolt !