Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL subquery: dealing with the maximum effective date

Re: SQL subquery: dealing with the maximum effective date

From: Brian Neary <bneary_at_containerworld.com>
Date: Mon, 18 Dec 2000 18:00:32 -0800
Message-ID: <Ziz%5.131398$47.2066695@news.bc.tac.net>

I'm not positive this is what you wanted, but, I hope it helps:

Select SSN,record_num from table1 t1 where effdt <= (Select max(effdt) from table1 t2 where t1.ssn = t2.ssn and t2.status = 'active')

Of course this will not work if you have duplicate ssn/effdt/status rows with an active status.

Brian

<crunchy2k_at_my-deja.com> wrote in message

<crunchy2k_at_my-deja.com> wrote in message news:91jp34$l0u$1_at_nnrp1.deja.com...
> I have a table with fields of
>
> SSN
> record_num (1, 2, etc.)
> effdt
> status ('active', 'inactive')
> ... and other fields
>
> Conceptually, I know that each SSN has one maximum effdt with status =
> active, when I look across all of its record_num's.
>
> For each SSN, I want the SSN/record_num combinations that were active on
> that one maximum effdt that was active.
>
> Can someone give me a SQL query for this? I am guessing it entails a
> subquery within another subquery.
>
> Thanks.
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Mon Dec 18 2000 - 20:00:32 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US