MS SQL return records where the most recent matches the criteria

Question!

This is a question using SQL Server. Is there a more elegant way of doing this?

Considering a table mytable (I'm using Unix timestamps, I've converted these to readable dates for 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 would like to extract the Foreign_ID where the most recent record's (highest ID) date is in a range, which is this example is the 1st January 2016 to 31st December 2016. The following works if substituting the dates for timestamps:

select distinct 
    Foreign_ID 
from 
    mytable l1 
where 
    (select top 1 Date 
     from mytable l2 
     where l2.Foreign_ID = l1.Foreign_ID 
     order by ID desc) >= **1 Jan 2016**  
    and 
    (select top 1 Date 
     from mytable l2 
     where l2.Foreign_ID = l1.Foreign_ID 
     order by ID desc) <= **31 Dec 2016**

That should only return Foreign_ID = 3. A simpler query would also return Foreign_ID 2 which would be wrong, as it has a more recent record dated out of the above ranges

This will all form part of a bigger query

By : R Taylor


Answers

If it's SQL Server 2008+, you can use this:

select foreign_id
from (
    select foreign_id, row_number() over (order by id desc) as rseq
    from myTable 
    where Date >= value1 and Date <= value2
) as x
where rseq = 1

Just fill in the date values, and you might have to put brackets or quotes around the column named "Date", since it is also a keyword.



Assuming SQL Server 2005+, you can use ROW_NUMBER:

WITH CTE AS
(
    SELECT  *,
            RN = ROW_NUMBER() OVER( PARTITION BY Foreign_ID 
                                    ORDER BY ID DESC)
    FROM dbo.YourTable
    WHERE [Date] >= '01-Jan-2016'   -- you need to use the right 
    AND [Date] <= '31-Dec-2016'     -- date format here
)
SELECT Foreign_ID
FROM CTE
WHERE RN = 1;
By : Lamak


This video can help you solving your question :)
By: admin