对sql服务器中的排序数据进行排序

Question

***和***中的查询将生成按status_date排序的分区行。现在，如果结果有多于一行的status_date，那么我只想按change_date排序那些行。

我想出了下面的查询，但它没有给出想要的结果。

SELECT MID,status_date,status,change_date, row_number() OVER (PARTITION BY MID ORDER BY change_date DESC) as RN1, 1 as TimeRange 
        FROM 
         (
        *** SELECT MID,status_date,status,change_date, 
         rn = row_number() OVER (PARTITION BY MID ORDER BY status_date DESC) 
         FROM listing_History (nolock) 
         WHERE MID like '12-572561' **** 

         ) AS A 

对于参考：

内部***的从查询结果和***：

12-572561 2012-06-11 00:00:00.000 Canceled 2012-06-11 14:53:22.957 1 
12-572561 2012-03-01 00:00:00.000 Canceled 2012-03-01 13:32:40.030 2 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-02 07:32:34.067 3 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.400 4 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-10 00:01:03.407 5 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.417 6 

与现有的查询结果：

12-572561 2012-06-11 00:00:00.000 Canceled 2012-06-11 14:53:22.957 1 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.417 2 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.400 3 1 
12-572561 2012-03-01 00:00:00.000 Canceled 2012-03-01 13:32:40.030 4 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-10 00:01:03.407 5 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-02 07:32:34.067 6 1 

期望的结果：

12-572561 2012-06-11 00:00:00.000 Canceled 2012-06-11 14:53:22.957 1 1 
12-572561 2012-03-01 00:00:00.000 Canceled 2012-03-01 13:32:40.030 2 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.417 6 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-06-11 07:43:29.400 4 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-10 00:01:03.407 5 1 
12-572561 2012-01-02 00:00:00.000 Active  2012-01-02 07:32:34.067 3 1 

Answer 1

您可以使用ORDER BY为change_date在查询

Answer 2

的ROW_NUMBER（）函数的结束并不确定订单的结果。你需要一个ORDER BY子句。如果您不需要ROW_NUMBER（）值的结果，你可以做以下

SELECT MID,status_date,status,change_date 
FROM listing_History (nolock) 
WHERE MID like '12-572561' 
ORDER BY status_date DESC, change_date DESC