SQL Group by MAX then get the MIN of all the groups

*edit: I’m using SQL Server 2012.

I have a table that looks like this:

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

I want to get this the latest LogOutTime for each id. Then I want to get the oldest LogOutTime out of that group.

This is the record I want:

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

This gives me the latest LogOutTime for each group:

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

This gives me the date that I want:

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

I need to get the ID and I’m not sure how to go about it and haven’t found an example that is like mine.

This is pretty easy with a derived table.

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')

Since we just need the lowest value, we can select the top 1 from the grouping query, and order it by date ascending.

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;

If you’re not using SQL Server specifically, you can likely use OFFSET/FETCH (though this will work on 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;

Hope this helps!

Depending on the DBMS, you can use LIMIT 1 or TOP 1 or FETCH FIRST 1 ROWS ONLY – in combination with 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 ;

Hi thanks for all the answers. This is what I came up with after as a solution.

SELECT *
    FROM (
            SELECT MAX(LogOutTime) AS MaxLogOut,
                   id
            FROM table1
            GROUP BY id
    ) AS table2
    WHERE MaxLogOut = (SELECT MIN(MaxLogOut) 
                       FROM (
                            SELECT MAX(LogOutTime) AS MaxLogOut,
                                    id
                             FROM table1
                             GROUP BY id) AS table3
                        )