iterate through table and select most current record

[MSBrady]

SQL Server 2005

I feel dumb for asking this but I can't figure it out. I need help to iterate through an employee status table and select only the most current record either using a View or SP.
Ex.
Name Status Effective Date
Sally Someone H2 11/03/2006
Sally Someone H1 09/15/2005
Norm Noone H1 03/23/2003
Nick Anyone H2 02/21/2000

How whould I limit the result set only the most current Effective Date so as to get the current status. They don't override the status field in this table, they had a new record whenever someone changes status.

thanks

 

[gmmastros]

Select *
From   Table
       Inner Join (
         Select Name, Max(EffectiveDate) As MaxDate
         From   Table
         Group By Name
         ) As A
         On Table.Name = A.Name
         And Table.EffectiveDate = A.MaxDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

I would go with George's code, but alter it a little as a SELECT * could cause performance issues depending on the size of the records.

SELECT Table.* 
...

or 

SELECT Table.Name, Table.Status, Table.EffectiveDate, 
...

is what I would use because a SELECT * would pull both the main Table records and the A. records all in one line and you probably don't want that.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[MSBrady]

thanks all, I'll try it today.