SQL Server return records where the most recent matches the criteria -

this question using sql server. there more elegant way of doing this?

considering table mytable (i'm using unix timestamps, i've converted these readable dates ease of reading)

id  foreign_id  date ------------------------- 1   1           01-jul-15 2   2           01-sep-16 3   3           05-aug-16 4   2           01-sep-15 

i extract foreign_id recent record's (highest id) date in range, example 1st january 2016 31st december 2016. following works if substituting dates timestamps:

select distinct      foreign_id       mytable l1       (select top 1 date       mytable l2       l2.foreign_id = l1.foreign_id       order id desc) >= **1 jan 2016**       ,      (select top 1 date       mytable l2       l2.foreign_id = l1.foreign_id       order id desc) <= **31 dec 2016** 

that should return foreign_id = 3. simpler query return foreign_id 2 wrong, has more recent record dated out of above ranges

this form part of bigger query

assuming sql server 2005+, can use row_number:

with cte (     select  *,             rn = row_number() over( partition foreign_id                                      order id desc)     dbo.yourtable     [date] >= '01-jan-2016'   -- need use right      , [date] <= '31-dec-2016'     -- date format here ) select foreign_id cte rn = 1;