DATE型の日付とTIME型の時刻を結合してDATETIME型の日時が最新のレコードの取得

Question

DATE型の日付とTIME型の時刻を結合してDATETIME型の日時を算出し、その日時が最新のレコードを取得したいです。
日時がMAX()であるレコードを取得しようとしたのですが、下記の様に同じようなことを何回も書いてあるクエリになってしまいました。

テーブル定義

CREATE TABLE test_table (
  date DATE,
  time TIME,
  group_name NVARCHAR(10),
  value INT
)

データ挿入

INSERT INTO test_table(
  date,
  time,
  group_name,
  value
)VALUES
('2020-01-01', '10:00', 'groupA', 10),
('2020-01-10', '11:00', 'groupA', 20),
('2020-01-08', '15:00', 'groupA', 30),
('2020-01-11', '12:00', 'groupA', 40),
('2020-01-02', '10:00', 'groupB', 50),
('2020-01-03', '13:00', 'groupB', 60)

group_nameがgroupAであり、かつ日時が最新のものを取得しようとすると下記になってしまいました。

SELECT
  date,
  time,
  group_name,
  value,
  datetime
FROM
(
SELECT
  date,
  time,
  group_name,
  value,
  CONVERT(DATETIME, CONVERT(NVARCHAR, date, 111) + ' ' + CONVERT(NVARCHAR, time, 108)) AS datetime
FROM
  test_table
WHERE
  group_name = 'groupA'
) AS tmp_tbl1
WHERE
  datetime = (SELECT MAX(CONVERT(DATETIME, CONVERT(NVARCHAR, date, 111) + ' ' + CONVERT(NVARCHAR, time, 108)))
  FROM
      test_table
  WHERE
    group_name = 'groupA'
  )

データベースはSQL Serverですが、MicrosoftのPower BIからアクセスしている関係上、SELECTしか使用できず、WITHなどは使えません。
WITHが使用できれば下記の様にできると思っています。

WITH sub_query AS (
SELECT
  date,
  time,
  group_name,
  value,
  CONVERT(DATETIME, CONVERT(NVARCHAR, date, 111) + ' ' + CONVERT(NVARCHAR, time, 108)) AS datetime
FROM
  test_table
WHERE
  group_name = 'groupA'
)

SELECT
  date,
  time,
  group_name,
  value,
  datetime
FROM
  sub_query
WHERE
  datetime = (SELECT MAX(datetime) FROM sub_query)

SELECTのみでも、加工した列の最大値のレコードの取得について、もっとスマートな記載はできないでしょうか？

Answer 1

Window関数を使用するのが一番簡潔な記述になるかと思います。
ROW_NUMBER()やDENSE_RANK()を用います。

select * 
from (
  SELECT  *
        , row_number() over(order by date desc, time desc) AS seq
  FROM  test_table
  WHERE group_name = 'groupA'
) t1
where seq=1

Comments

[BrandNewMenu]

ありがとうございます。
もし最大のものが複数存在していた場合に、それらをすべて表示したい場合はどうすれば良いでしょうか？

[sazi]

RANK()を使用します。RANKは同位は同じ数字になるので。