SQL Group by MAX 然後得到所有組的 MIN

*編輯：我使用的是 SQL Server 2012。

我有一個看起來像這樣的表：

id     LogOutTime
------------------------------
8563   2017-11-21 09:21:28.330
7961   2017-11-21 09:22:40.627
7961   2017-11-21 09:26:48.507
8563   2017-11-21 09:29:05.977

我想為每個 id 獲取最新的 LogOutTime。然後我想從該組中獲取最舊的 LogOutTime。

這是我想要的記錄：

id     LogOutTime
------------------------------
7961   2017-11-21 09:26:48.507

這為我提供了每個組的最新 LogOutTime：

SELECT MAX(LogOutTime) AS MaxLogOut,
      id
FROM table1
GROUP BY id

這給了我想要的日期：

SELECT MIN(table2.MaxLogout)
FROM (SELECT MAX(LogOutTime) AS MaxLogOut,
            id
     FROM table1
     GROUP BY id) AS table2

我需要獲取 ID，但我不知道該怎麼做，也沒有找到像我這樣的例子。

使用派生表這很容易。

CREATE TABLE #thing (id  INT, dateval DATETIME)

INSERT #thing ( id, dateval )
VALUES 
(8563,   '2017-11-21 09:21:28.330'),
(7961,   '2017-11-21 09:22:40.627'),
(7961,   '2017-11-21 09:26:48.507'),
(8563,   '2017-11-21 09:29:05.977')

由於我們只需要最小值，因此我們可以從分組查詢中選擇前 1 個，並按日期升序對其進行排序。

SELECT   TOP 1 *
FROM     ( SELECT t.id, MAX(t.dateval) AS max_date FROM #thing AS t GROUP BY t.id ) AS x
ORDER BY x.max_date;

如果您沒有專門使用 SQL Server，您可能會使用 OFFSET/FETCH（儘管這適用於 SQL Server 2012+）。

SELECT   *
FROM     ( SELECT t.id, MAX(t.dateval) AS max_date FROM #thing AS t GROUP BY t.id ) AS x
ORDER BY x.max_date OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY;

希望這可以幫助！

根據 DBMS，您可以使用LIMIT 1orTOP 1或FETCH FIRST 1 ROWS ONLY- 與ORDER BY MaxLogOut：

-- standard SQL, Postgres, SQL Server 2012+
SELECT 
   MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
   OFFSET 0 ROWS
   FETCH FIRST 1 ROWS ONLY ;

-- SQL Server
SELECT TOP (1)  
   MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut ;

-- MySQL
SELECT 
   MAX(LogOutTime) AS MaxLogOut, id
FROM table1
GROUP BY id
ORDER BY MaxLogOut
   LIMIT 1 ;

引用自：https://dba.stackexchange.com/questions/191424