Re: SQL subquery: dealing with the maximum effective date

i haven't gotten the chance to test this, but this should set you off on the right direction. some might be able to come up with a more efficient query though, not that i'm saying this query is inefficient.

select a.ssn,a.record_num
from your_table a, (select ssn, max(effdt) from your_table) b where a.ssn = b.ssn
and a.effdt = b.effdt;

cheers,
bb

In article <91jp34$l0u$1_at_nnrp1.deja.com>,   crunchy2k_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Mon Dec 18 2000 - 07:57:23 CST