グルーピングした内の最大値を持つレコードを取得

グルーピングした内の最大値を持つレコードを取得しようとして、下記の解説記事が良かった。

not existsを使うのが最速。row_number()で順番つけるのが意図がわかりやすい。 group by使うのはいまいち。

【SQL】グループごとに最大の値を持つレコードを取得する方法3選 | Takakisan

方法2: row_number()

select
  *
from
  (
    select
      *,
      row_number() over (
        partition by
          user_id
        order by
          created_at desc
      ) rownum
    from
      user_scores
  ) with_rownum
where
  rownum = 1
;

row_number() に関しては MySQL 8 から使えるみたい。

たとえば、カラムA の値の降順で連番を振りたい時は次のように指定します。

SELECT ROW_NUMBER() OVER (ORDER BY カラムA DESC);

MySQL の ROW_NUMBER() - MySQL の基礎 - MySQL 入門

方法3: not exists

このクエリが読み解くのが難しかった。

select
  *
from
  user_scores
where
  not exists (
    select
      1
    from
      user_scores sub
    where
      user_scores.user_id = sub.user_id
      and user_scores.created_at < sub.created_at
  ) -- user_idが同じで、created_at がより大きいレコード が存在しない
;

1つ目の条件で、大元のfromにしているレコードと、exists内でfromにしているレコードで、同じuser_idのものを探します。 さらに2つ目の条件で、大元のfromにしているレコードより、created_atが大きいレコードを探します。

not existsですので、他にcreated_atが大きいレコードがあったら除外です。 そうすると、created_atの大きさ比べで最後まで勝ち抜いたレコード達だけが選抜される訳です。これでやりたいことが実現できましたね。

パフォーマンス的にはこの方法３のやり方が一番良いみたい。

ただ、最大値が複数あったときに複数取得される点は気をつけたい。