在多个变量上使用 RANK 的快速帮助 [英] Quick help using RANK over multiple variables

问题描述

我需要一点帮助，在 SQL Server 2008 中为以下内容编写 SELECT 语句:(示例表)

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   

对于每个唯一的 ProductID-Year 组合(例如 01-2009、03-2011)，我需要获取最新日期的行.实际数据并没有那么好组织——01-2009 可能只有 1 条记录，03-2009 可能只有 15 条记录.

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.

我想我必须使用 DENSE RANK 但我不确定.

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

推荐答案

row_number 应该足以满足您的需求.

row_number should be sufficient for your needs.

注意:我假设您的 Date 列是真正的 Date 或 DateTime 数据类型，而不是您显示的表单中的字符串.如果该假设是错误的，则需要进行一些额外的字符串操作才能将 Date 转换为可排序的格式.

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

这篇关于在多个变量上使用 RANK 的快速帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！