The FileDate is used to label the last record coming into the database. Each month we have a lot of duplicate records coming into the table. If the LeaseID,Year, and Month are the same, I need to go with the last that was load. It would be the record with the FileDate of 200807.
OK, With "last" you mean the most recently added record. Then you should use DESC.
But I think what you really need is this:
WITH CTE AS
(SELECT LeaseID, OpID, Year, Month, MCF, FileDate,
ROW_NUMBER() OVER (PARTITION BY LeaseID, OpID, Year, Month ORDER BY FileDate DESC) AS RowNum
WHERE RowNum = 1
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages