如何根据类别和日期排除相同ID的行

Question

+--+-------+----------+---+
|ID|OrderID|OrderDate |Cat|
+--+-------+----------+---+
|1 |240904 |23/05/2013|63 |
+--+-------+----------+---+
|1 |338584 |11/12/2013|64 |
+--+-------+----------+---+
|1 |47309  |21/11/2011|64 |
+--+-------+----------+---+
|2 |175307 |23/11/2012|63  |
+--+-------+----------+---+
+--+-------+----------+---+
|2 |195307 |24/12/2012|64  |
+--+-------+----------+---+
+--+-------+----------+---+
|2 |175300 |23/11/2011|63  |
+--+-------+----------+---+
+--+-------+----------+---+
|5 |175307 |23/11/2012|63  |
+--+-------+----------+---+
+--+-------+----------+---+
|5 |215307 |15/1/2013|64  |
+--+-------+----------+---+
+--+-------+----------+---+
|7 |195307 |15/06/2012|63  |
+--+-------+----------+---+

我正在使用SQL Server 2008 R2。我试图（努力）找到一种方法，以获取Cat 64的最新OrderDate，然后在同一ID上获得Cat 63，从表格上所有的ID上，这些记录看起来像上面的|。

Answer 1

尝试像这样

Select OrderID,OrderDate From
(
Select *,ROW_NUMBER() over(PARTITION by ID,CAT Order by OrderDate DESC) AS RN
From tbl
) AS Tmp
Where RN = 1

工作小提琴