Question :
*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.
Answer :
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
)