0

*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.

0

3 Answers 3

2

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!

2

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 ;
0

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
                        )
1
  • 1
    Seems correct but a bit overcomplicated. Nov 22, 2017 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.