Выберите последнюю запись с дополнительными условиями

Трудно дать ответ, не зная продукта базы данных.

1. if there is no auto_increment/identity column 
2. and if there is no other primary key (which is a bad idea obviously) 
3. and if the given database product supports `CURRENT_TIMESTAMP` (each DBMS will likely have some equivalent to the current date and time) 
4. and if the target date by which you measure "latest" is the current date and time


Select Id, Level
From Table As T
Where T. EffectiveDate =    (
                            Select Max(T2.EffectiveDate)
                            From Table As T2
                            Where T2.ID = T.ID
                                And ( T2.EffectiveDate Is Null
                                    Or (
                                        CURRENT_TIMESTAMP >= T2.EffectiveDate
                                        And CURRENT_TIMESTAMP <= T2.ExpirationDate
                                        )
                                    )
                            )   

Вы заметите ряд предостережений в моем ответе. Это указывает на то, что нам нужно больше информации:

1. Какой продукт базы данных и версия?
2. Есть ли в таблице уникальный ключ auto_incrementing?
3. Как Уровень вписывается в желаемые результаты? (Пожалуйста, расширьте свои образцы данных, чтобы включить крайние случаи).
4. Что должно произойти, если текущая дата и время предшествуют дате вступления в силу с нулевой датой истечения срока действия?

ИЗМЕНИТЬ

Теперь, когда мы знаем, что вы используете SQL Server 2008, решение упрощается:

If object_id('tempdb..#Test') is not null
    Drop Table #Test;
GO
Create Table #Test (
                    PkCol int not null identity(1,1) Primary Key
                    , Id varchar(50) not null
                    , Level int not null
                    , EffectiveDate datetime not null
                    , ExpirationDate datetime null
                    );

Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',2,'12/01/2005',NULL);
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',1,'12/01/2005',NULL); 
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-12',2,'12/01/2005','01/01/2009');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'10/01/1994','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'01/01/1999','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',2,'09/01/2001','11/30/2005');
Insert #Test( Id, Level, EffectiveDate, ExpirationDate ) Values ('000012-A12',1,'12/01/2005','12/31/2007');

With Items As
    (
    Select PkCol, Id, Level, EffectiveDate, ExpirationDate
        , Row_Number() Over (   Partition By Id
                                Order By EffectiveDate Desc, Coalesce(ExpirationDate,'99991231') Desc, Level Asc ) As Num
    From #Test
    )
Select PkCol, Id, Level, EffectiveDate, ExpirationDate
From Items
Where Num = 1

В вашем образце вывода у вас есть комбинация ('000012-A12',2,'12/01/2005','01/01/2009'), которой нет в ваших исходных данных.

Я использую две функции, которые были добавлены в SQL Server 2005: общие табличные выражения и функции ранжирования. Выражение общей таблицы Item действует как представление на месте или запрос. В функции ранжирования Row_Number происходит настоящее волшебство. Как следует из названия, он возвращает последовательный список чисел, упорядоченных по предложению Order By. Однако он также перезапускает нумерацию для каждого значения идентификатора (это бит Partition By). Фильтруя по Num = 1, я возвращаю «верхнее» значение для каждого идентификатора.