*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.
Best Answer
This is pretty easy with a derived table.
Since we just need the lowest value, we can select the top 1 from the grouping query, and order it by date ascending.
If you're not using SQL Server specifically, you can likely use OFFSET/FETCH (though this will work on SQL Server 2012+).
Hope this helps!