『ながっちゃん』のページ

気まぐれ兄ちゃんの独り言

#4[ACS]あるフィールドでグループ化し、グループ化されたレコードの中でもう１つのフィールドが最大値となるレコードを求めるクエリ。

内容

３つのフィールド「f1」、「f2」、「f3」があるテーブルにおいて、「f1」の値ごとに最大値の「f2」のレコードの「f1」、「f2」、「f3」の値を抽出するクエリの作り方です。

解説

　このクエリは２つのクエリに分けて考えます。１つはフィールド「f1」でグループ化しフィールド「f2」の最大値を求めるクエリです。もう１つは最初のクエリとリンクして レコードの値を抽出するクエリです。

サンプル

テーブル「備品履歴」を作成します。このテーブルは備品を管理するためのテーブルで、フィールド「MainID」が備品の管理番号を表し、フィールド「SubID」は備品更新の履歴を表す番号でデータを入力した順につける連番となっています。プライマリキーはフィールド「SubID」です。
サンプルテーブル

このテーブルには次のようなデータを入れます。
サンプルレコード

このテーブルからフィールド「MainID」別にフィールド「SubID」の最大値を求めるクエリ「q1」を作成します。
サンプルクエリ1

つぎにクエリ「q1」とリンクしてフィールド「MainID」のレコードを抽出するクエリを作成します。
サンプルクエリ2

このクエリを実行すると次のようにフィールド「MainID」別に最大のフィールド「SubID」の値を持つレコードの全フィールドの値が表示されます。 フィールド「MainID」別、つまり備品別にフィールド「SubID」の最大の値、つまり最後に入力されたレコードの全フィールドの値が表示されます
クエリの処理結果

おまけ

上述のように本問題を解決するクエリを２つのクエリの組み合わせで作成しましたが、これら２つのクエリは１つのクエリにすることも可能です。クエリをSQLビューで表示して次のクエリを入力します。

SELECT 備品履歴.MainID, 備品履歴.SubID, 備品履歴.日付, 備品履歴.内容
FROM (SELECT 備品履歴.MainID, Max(備品履歴.SubID) AS SubIDの最大
FROM 備品履歴 GROUP BY 備品履歴.MainID) q1 
INNER JOIN 備品履歴 ON q1.SubIDの最大 = 備品履歴.SubID;

このクエリは上述の２つのクエリと全く同じ結果を得られます。実はこのクエリは、上述のクエリ「クエリ1」

SELECT 備品履歴.MainID, 備品履歴.SubID, 備品履歴.日付, 備品履歴.内容
FROM q1 INNER JOIN 備品履歴 ON q1.SubIDの最大 = 備品履歴.SubID;

の"FROM"と"q1"の間にクエリ「q1」のSQLビューの内容を追加しているだけです。
クエリが複雑になるという欠点はありますが、クエリの数を減らしたい場合にはこのように複数のクエリを1つにする方法があることを覚えておいても損はないでしょう。特に、プログラムでクエリを作成する場合は、複数クエリを作成するより1つのクエリを作成する方がプログラムがシンプルになります。