SELECT文での件数の制限

Question

sql文の組み立て方がわかりません。
userテーブルにはidとusernameのカラムがあり、loginhystoryテーブルにはidとuser_id、created(ログイン日時)のカラムがあります。userがログインすることでloginhystoryテーブルにデータが追加されていくというものです。loginhystoryテーブルには同じuser_idをもつデータが複数あるのですが、その中で最新のログイン日時だけを取り出すsqlの組み立て方がわかりません。

select ur.username, lh.created
from users ur
left join loginhystory lh on lh.user_id = ur.id

select ur.user, lh.created
from users ur, loginhystory lh
where ur.id = lh.user_id

これらのsql文だとどちらもusernameとログイン日時の対応がすべて得られ、ログインしていない場合はnullが得られるのですが、1userに対してすべてのログイン日時が得られてしまいます。欲しいのは各userの最新ログイン日時1件ずつです。どのようにすれば各ユーザー1件ずつのログイン日時が得られるでしょうか。よろしくお願い致します。

Answer 1

「最後のログイン日時」=「ログイン日時の最大値」なので、GROUP BYをかけた上でMAX()関数を使いましょう（ユーザー名は1通りしかありませんが、何も指定しないとどのデータを抽出するか判断がつかないので、便宜上MAX()を挟んであります）。

select MAX(ur.username) AS uname, MAX(lh.created) as last_login
from users ur
left join loginhystory lh on lh.user_id = ur.id
group by ur.id

Comments

[退会済みユーザー]

SQL文としてわかりやすく、やりたいことが実現できました。ありがとうございました。

Answer 2

lh.createdで降順にソート(order by)してやって、1件目を取ればいいのでは？

DBによって異なるのですが、
Oracleならwhere句にrownum = 1の条件を追加してあげれば、結果は1レコードのみになります。
MySQLならlimit句をつけてあげれば同様の結果になります。

参考)
・Oracle ->先頭から指定件数のレコードを取得する（ROWNUM）
・MySQL ->取得データの行数を制限

--追記--
すいません、nullがありうることを失念してました。
ソートする際はDBによってnullの扱い方が違うので注意が必要です。

・Oracle -> nullは「一番大きな値」として扱う
・MySQL -> nullは「一番小さな値」として扱う
だからみなさん、max()してたんですね。

Comments

[退会済みユーザー]

MySQLを使っているのですが、そのような命令はご存知でしょうか。今調べてみたところユーザー変数を使うというような方法が見つかったのですが、命令という感じではありませんでした。

[kaputaros]

ん？命令とは、limit句のことでしょうか？nullの扱いについてでしょうか？？
limit句については、リンク先を確認してみてください。

nullの扱いについては説明不足でしたね。
これは、order by で指定したカラムにnullが含まれていた場合のnullの扱われ方です。
MySQLだと「order by lh.created」するとnullが最初にきてしまうんです。
descを指定して降順にすれば、nullは最後になるので問題はないのですが･･･。

[退会済みユーザー]

命令という表現がおかしかったですね。すみません。oracleで言うところのrownumのようなものはMySQLにもあるでしょうか。nullのデータもあるのでソートしてrownumを指定するというような方法は使いにくいとは思うのですが、あるのかどうかを知りたいです。実際、ソート時のnullの扱いについては考えたこともなかったのですが勉強になりました。追記ありがとうございます。

[kaputaros]

回答にある「取得データの行数を制限」のリンク先は確認されましたでしょうか？
使い方の書式として
「SELECT col_name, ... FROM tbl_name LIMIT [offset,] row_count;」
と書いてあるかと思いますが･･･。

row_count が oracle の rownum に相当するのかな？用法はちがうかと思いますが。

order by ～ desc と limit 1 を組み合わせればいいのでは？

[退会済みユーザー]

LIMIT句に入れる数字がoracleのrownumに対応するのですね。取得する全件数を制限する方法としてLIMIT句を使うのはわかるのですが、各ユーザーごとに最新の1件を取得するという制限をするときにもLIMIT句は使えるのでしょうか。
自分がやってみたこととしては

select ur.username, lh.created
from users ur, loginhystory lh
where ur.id = lh.user_id
order by ur.id, lh.created desc

とすることで各ユーザーの全ログイン日時が最新のものから順に得られたのでこれを単純にgroup byして

select ur.username, lh.created
from users ur, loginhystory lh
where ur.id = lh.user_id
group by ur.id
order by ur.id, lh.created desc

としたところ、ユーザー名と最も古いログイン日時1件の対応データになってしまいました。LIMIT句などを使えば欲しいものは得られるのでしょうか。

[kaputaros]

自分がちょっと勘違いしていました。複数ユーザー分も取得するんですね。
limit は何かを１件に特定することは出来ると思いますが、今回のでは使えなさそうです^^;

ちなみに Mysqlではgroup by を指定すると、ascされた状態で取得されるそうなので、
group by句でdescを指定してあげるといいみたいです。

参考：データをグループ化(http://www.dbonline.jp/mysql/select/index9.html)

Answer 3

loginhystoryテーブルに対して、「user_id」でグループ化し「created」の最大値（max）を取得すると期待するデータが取得できるかと思います。
userテーブルと結合する必要がある場合、下記SQLとuserテーブルを外部結合して利用してみてください。

select
  user_id, max(created)
from
  loginhystory
group by
  user_id

Comments

[退会済みユーザー]

考え方としてはわかったのですが、実際に組み立てるとなると自分ではできませんでした。ヒントを与えていただきありがとうございました。