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.

  • 1
    Which DBMS is the query for? – ypercubeᵀᴹ Nov 21 '17 at 17:34
  • I just edited my code. I left in the incorrect field name, it should be GROUP BY id. Thanks. – Dave85709 Nov 21 '17 at 17:43
1

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!

1

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
                        )
  • Seems correct but a bit overcomplicated. – ypercubeᵀᴹ Nov 22 '17 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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