SQLserverにてid毎の最新レコード取得SQLについて

Question

以下の様なテーブルに対してユーザ毎にlogintimeが最新のレコードを取得するSQLを作成しています。
最新レコードを対象とした記述に苦戦しているため、ご教示ください。

現在のSQLの原因として以下の点があると思います。
・selectのmaxはlogintimeの最新レコードではなく
グループ化されたuser_idの各カラム毎に最大値を取得している
・gorup byの結果に対してorder byを行っているので
最新レコードでの一意のグループ化が行われていない

↓ 現在作成中のSQL
select
user_id,
max(group_id) group_id,
max(logintime) logintime
from histories
group by user_id
order by logintime desc

↓ 対象のTBL
histories.tbl
～～～～～～～～～～～～～～～～
user_id group_id logintime
～～～～～～～～～～～～～～～～
1 2 12:00
1 1 15:00
2 1 16:00
3 1 17:00

↓ 期待する結果
～～～～～～～～～～～～～～～～
user_id group_id logintime
～～～～～～～～～～～～～～～～
1 1 15:00
2 1 16:00
3 1 17:00

↓現在の結果
～～～～～～～～～～～～～～～～
user_id group_id logintime
～～～～～～～～～～～～～～～～
1 2 15:00
2 1 16:00
3 1 17:00

Answer 1

SQLServerの分析関数の使用例の「4. 最大値の行の取得」
https://oraclesqlpuzzle.ninja-web.net/sqlserver2008-sql1-olap.html#1-4
を見ながら、Oracle11gR2で作ってみました :-)

with t(user_id,group_id,logintime) as(
select 1,2,'12:00' from dual union
select 1,1,'15:00' from dual union
select 2,1,'16:00' from dual union
select 3,1,'17:00' from dual),
tmp as(
select user_id,group_id,logintime,
max(logintime) over(partition by user_id) as MaxLogintime
  from t)
select user_id,group_id,logintime
  from tmp
 where logintime = MaxLogintime
order by user_id;

USER_ID   GROUP_ID  logintime
-------  ---------  ---------
      1          1  15:00
      2          1  16:00
      3          1  17:00

Comments

[Zuishin]

これもしかして 24 時間 24×60(×60(×100)) 分書くんですか？

Answer 2

これでどうですか？

select * from histories h1 where logintime=(select max(logintime) from histories h2 where h1.user_id = h2.user_id) order by logintime

Comments

[yui_sarasina]

ご回答ありがとうございます。
投稿後に試行錯誤した結果、頂いたSQLと同様の書き方に至りました。
今回はこちらの書き方で進めたいと思います。

Answer 3

目的が、

テーブルに対してユーザ毎にlogintimeが最新のレコードを取得するSQL

であれば、自身で出来る様になるまでは、簡単な処でまずは済まして、
集合を含めたＳＱＬの学習を行います。
１．先ずは目的に応じたデータの種類のビューを作ります。
⇒元のテーブルから、ユーザー毎のビューを作る
２．１．で作成したビューに対して、logintimeの最新を取得する。
⇒当座は、この結果を、ユーザー順にくっつけて運用すれば、事足りる筈です。
（UNION 集合演算子）
３．学習結果を基に、元テーブルからユーザー毎の現在行いたいことを試行錯誤します。
⇒ユーザー数などは多くなりがちなので、個別にビューを作ったりしないでを
期待しますが、出来ない間は無理をしないで、
目的の内容が含まれる、ビューを作り、そこから抽出する方が簡単です。
複数のビューで作られた集合が、ユーザー毎のデータの集合なのですから。
’
MS SQL Server 自習書
で、検索されると、MS-SQL Server のマイクロソフト版の自習書が検索できます。
ミック リレーショナル・データベースの世界
ミック氏のサイトが検索できます、一度は目を通して方が良いかも。

Comments

[yui_sarasina]

丁寧なご説明ありがとうございます。
１つ１つ順番に進めていくことと
MS SQL Server 自習書を検索して勉強したいと思います。

[daive]

少し余裕が出来たら、
Visual Studio LightSwitch
https://msdn.microsoft.com/ja-jp/library/ff851953.aspx
を学習してみてください。VS2010~ で使用可能な簡易言語的機能です。
テーブルが完成していれば、コードレスで（コードを書く事も可能）
データベースのデータ表示、編集機能が簡単に作成可能です。