Quick Reference Using RANK on 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 combination of ProductID-Year (for example, 01-2009, 03-2011) I need to take a row with the last date. Actual data is not well organized - there can be only 1 record for 01-2009 and 15 records for 03-2009.

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

Answer 1

row_number should be enough for your needs.

. , "" DateTime, ​​, . , .

;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