subquery to find all rows active on the max date that is active

I am re-submitting my question, because I left off 'effdt' (see the ** line).

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 a maximum effdt with status = active, when I look across all of its record_num's.

• For each SSN, I want the SSN/record_num/effdt combinations that were active on that maximum effdt where the status is 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 - 15:12:00 CST