CREATE TABLE #Test
(
DisplayOrder int,
PostedDate date,
ImageName nvarchar(50)
)
declare @DateToSelect datetime
set @DateToSelect = '9/1/2013'
insert into #Test values (1, '9/1/2013', 'Img1')
insert into #Test values (2, '9/1/2013', 'Img1')
insert into #Test values (2, '10/1/2013', 'Img1New')
insert into #Test values (3, '9/1/2013', 'Img1')
select
#Test.DisplayOrder,
#Test.PostedDate,
#Test.ImageName
from
#Test
join
(
select
displayOrder,
Max(PostedDate) as 'PostedDate'
from
#Test
where PostedDate <= @DateToSelect
group by displayOrder
) as SQ on (SQ.DisplayOrder = #Test.DisplayOrder) AND (SQ.PostedDate = #Test.PostedDate)
DROP TABLE #Test