SQLで最新のレコードのみを取り出す方法

概要

事例として、購買情報のテーブルを用意しました。
1ユーザは複数の購買IDを持つことが可能です。

🔽入力となるテーブル(テーブル名 : purchases)🔽

purchase_id	user_id	purchase_date	price
1	A	03/01	¥40,000
2	A	01/01	¥35,000
3	B	01/01	¥20,000
4	A	02/01	¥5,000
5	B	04/01	¥10,000
6	C	02/01	¥20,000

今回、各ユーザの最新の購買レコードのみ抽出したテーブルを作成したいとします。
🔽目標となるテーブル🔽

purchase_id	user_id	purchase_date	price
1	A	03/01	¥40,000
5	B	04/01	¥10,000
6	C	02/01	¥20,000

最新レコードのみ抽出するために、「GROUP BYやを使え！」みたいな記事も多いのです。
しかし、GROUP BY対象になるカラム(user_id)や、ORDER BY対象になるカラム(purchace_date)以外のカラム情報も取得したいときに、GROUP BYだとできません。😭

「では、どうやってやるのか？」について、忘備録として残します。

方法

① グループ内で新しい/古いもの順の連番をつける

ROW_NUMBER() OVER(PARTITION BY {グループ化する列} ORDER BY {並びかえる列} ASC/DESC)
とすることで、{並びかえる列}を昇順・降順に並び替え、グループごとに連番（順位）をふることが可能です。

具体例を見てみましょう。

select 
  *
 from (
   select
    *,
    row_number() over (PARTITION BY user_id ORDER BY purchase_date DESC) AS number
    FROM purchases
  )

を用いることによって、以下のテーブルになります。

purchase_id	user_id	purchase_date	price	number
1	A	03/01	¥40,000	1
2	A	01/01	¥35,000	3
3	B	01/01	¥20,000	2
4	A	02/01	¥5,000	2
5	B	04/01	¥10,000	1
6	C	02/01	¥20,000	1

② 値が1のものだけ取得する。
次にwhere number = 1のものを取得すれば、最新のレコードのみになります！

結論

以下のコードでOK！

 select 
  *
 from (
   select
    *
    row_number() over (PARTITION BY user_id ORDER BY purchase_date DESC) AS num
    FROM purchases
  )
  where num = 1

もし違ういいやり方知っていれば、是非教えてください！

補足

順位づけの関数はrankとrow_numberの２つがあるが、row_numberを推奨。
理由は、作成日が同一のレコードがあった際に、rankだと同じ数字が複数現れるから。