Quick help using RANK over multiple variables

Question

I need a little help writing a SELECT statement for the following in SQL Server 2008: (example table)

  Date                       ProductID                       Year                       Price   
  01-01-10                   01                              2009                       1.00   
  02-01-10                   01                              2009                       2.00   
  03-01-10                   01                              2010                       3.00   
  04-01-10                   01                              2010                       4.00   
  05-01-10                   01                              2011                       5.00   
  06-01-10                   01                              2011                       6.00   
  01-01-10                   02                              2009                       1.00   
  02-01-10                   02                              2009                       2.00   
  03-01-10                   02                              2010                       3.00   
  04-01-10                   02                              2010                       4.00   
  05-01-10                   02                              2011                       5.00   
  06-01-10                   02                              2011                       6.00   
  01-01-10                   03                              2009                       1.00   
  02-01-10                   03                              2009                       2.00   
  03-01-10                   03                              2010                       3.00   
  04-01-10                   03                              2010                       4.00   
  05-01-10                   03                              2011                       5.00   
  06-01-10                   03                              2011                       6.00   
  01-01-10                   04                              2009                       1.00   
  02-01-10                   04                              2009                       2.00   
  03-01-10                   04                              2010                       3.00   
  04-01-10                   04                              2010                       4.00   
  05-01-10                   04                              2011                       5.00   
  06-01-10                   04                              2011                       6.00   

For each unique, ProductID-Year combination (e.g. 01-2009, 03-2011), I need to grab the line with the latest date. The actual data isn't so well-organized--there might only be 1 record for 01-2009, and 15 records for 03-2009.

I think I have to use DENSE RANK but I'm not sure.

Answer 1

row_number should be sufficient for your needs.

Note: I'm assuming your Date column is a true Date or DateTime datatype and not a string in the form you've shown. If that assumption is wrong, some additional string manipulation would be needed to convert Date into a sortable format.

;with cteRowNumber as (
    select Date, ProductID, Year, Price, 
           row_number() over (partition by ProductID, Year order by Date desc) as RowNum
        from YourTable
)
select Date, ProductID, Year, Price
    from cteRowNumber
    where RowNum = 1