SQL subquery: dealing with the maximum effective date

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 Sun Dec 17 2000 - 19:25:59 CST