Find the record nearest to the datetime specified

I am trying to retrieve the data from a table which is the closest to a specified date and time for example as you can see below little bit of data about doors and if they are open and closed at a specific time. I was to retrieve the list of doors grouped by DoorTitle closer to a specific time. The sample data is below

Id    DoorTitle    Status    DateTime
1     Door_1       OPEN      2019-04-04 9:16:22
2     Door_2       CLOSED    2019-04-01 15:46:54
3     Door_3       CLOSED    2019-04-04 12:23:42
4     Door_2       OPEN      2019-04-02 23:37:02
5     Door_1       CLOSED    2019-04-04 19:56:31

Lets say I want to find out the status of doors if they are open or closed at 2019-04-04 23:54:55 to give me the records grouped by door title nearest to that time. I have tried this query but it does give me the records closest to this time it just randomly selects them by checking if DateTime is less than 2019-04-04 23:54:55.

SELECT DoorTitle,Status,DateTime 
FROM Doors 
WHERE DateTime <= '2019-04-04 23:54:55'  
GROUP BY DoorTitle;

The results I want after running the query are the list of doors grouped by DoorTitle nearest to the date and time i.e 2019-04-04 23:54:55 specified as seen below it should return these results but the query I am using is not returning me this.

DoorTitle    Status    DateTime
Door_3       CLOSED    2019-04-04 12:23:42
Door_2       OPEN      2019-04-02 23:37:02
Door_1       CLOSED    2019-04-04 19:56:31

Let me know what sql query I can use to achieve this and how my query is wrong. Thanks in advance.

Answer

Don’t use aggregation. Use filtering:

SELECT d.*
FROM Doors d
WHERE d.DateTime = (SELECT MAX(d2.DateTime)
                    FROM doors d2
                    WHERE d2.DoorTitle = d.DoorTitle AND
                          d2.DateTime <= '2019-04-04 23:54:55'
                   );

Performance should be reasonable with an index on doors(DoorTitle, DateTime).

Advertisement

Answer