生产的具有多个字段的离散记录 [英] PRODUCED DISTINCT RECORD WITH MULTIPLE FIELDS

问题描述

我试图弄清楚如何从该表中提取数据：

Im trying to figure out how i could pull out the data out of this table:

表：状态

OrderNo   Status   Date        User
1          1       2011-07-01  user1
1          2       2011-07-05  user1
2          1       2011-07-01  user1
2          2       2011-07-06  user2
2          3       2011-07-10  user1

我需要产生以下结果：

OrderNo   Status   Date        User
1          2       2011-07-05  user1
2          3       2011-07-10  user1

实际上，我需要显示OrderNo和最后状态的DISTINCT列表。

Actually, what i needed is to display DISTINCT list of OrderNo and thier last status.

感谢所有可能的帮助。

干杯！

推荐答案

WITH S AS
(
    SELECT OrderNo, Status, Date, User
        , ROW_NUMBER() OVER
          (
              PARTITION BY OrderNo
              ORDER BY Date DESC
          ) Ranking
    FROM Status
)
SELECT OrderNo, Status, Date, User
FROM S
WHERE Ranking = 1

这篇关于生产的具有多个字段的离散记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！