Re: SQL subquery: dealing with the maximum effective date

i think i forgot to type in the group by clause in the inline view. it should have been: (select ssn, max(effdt) from your_table group by ssn).

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:59:11 CST