[解決済み] 各グループの上位1行を取得

質問

グループごとに最新のエントリーを取得したいテーブルがあります。以下はそのテーブルです。

DocumentStatusLogs テーブル

|ID| DocumentID | Status | DateCreated |
| 2| 1          | S1     | 7/29/2011   |
| 3| 1          | S2     | 7/30/2011   |
| 6| 1          | S1     | 8/02/2011   |
| 1| 2          | S1     | 7/28/2011   |
| 4| 2          | S2     | 7/30/2011   |
| 5| 2          | S3     | 8/01/2011   |
| 6| 3          | S1     | 8/02/2011   |

によってテーブルがグループ化されます。 DocumentID でソートされ DateCreated を降順で表示します。各 DocumentID 最新のステータスを取得したい。

私の希望する出力です。

| DocumentID | Status | DateCreated |
| 1          | S1     | 8/02/2011   |
| 2          | S3     | 8/01/2011   |
| 3          | S1     | 8/02/2011   |

• 各グループから上位のみを取得する集計関数はありますか？擬似コード参照 GetOnlyTheTop の下にあります。

  SELECT
    DocumentID,
    GetOnlyTheTop(Status),
    GetOnlyTheTop(DateCreated)
  FROM DocumentStatusLogs
  GROUP BY DocumentID
  ORDER BY DateCreated DESC
  
  

• もしそのような関数がない場合、私が望む出力を実現する方法はありますか？

• あるいはそもそも、データベースの非正規化が原因なのでしょうか？私が考えているのは、私が探しているのは1行だけなので、次のようにすればいいのではないかということです。 status も親テーブルにあるのでしょうか？

詳しくは、親テーブルをご覧ください。

現在 Documents テーブル

| DocumentID | Title  | Content  | DateCreated |
| 1          | TitleA | ...      | ...         |
| 2          | TitleB | ...      | ...         |
| 3          | TitleC | ...      | ...         |

親テーブルはこのようにすれば、簡単にそのステータスにアクセスできるでしょうか？

| DocumentID | Title  | Content  | DateCreated | CurrentStatus |
| 1          | TitleA | ...      | ...         | s1            |
| 2          | TitleB | ...      | ...         | s3            |
| 3          | TitleC | ...      | ...         | s1            |

アップデイト このような問題に簡単に対処できる "apply" の使い方を覚えました。

解決するには？

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

もし、1日に2つのエントリーを期待するならば、これは任意に1つを選びます。一日に両方のエントリを取得するには、代わりに DENSE_RANK を使用します。

正規化するかしないかについては、あなたが望むかどうかによります。

• 2か所でステータスを維持する
• ステータス履歴の保存
• ...

現状では、ステータスの履歴を保存しています。もし、親テーブルにも最新のステータスを表示したい場合（これは非正規化です）、親に "status" を維持するトリガーが必要になります。