[SOLVED] MYSQL datetime - Return all future dates plus most recent past date

MYSQL datetime - Return all future dates plus most recent past date

I have a MYSQL table that contains datetime and filename fields. I am trying to run a query which extracts only the most recent (past) document plus all future documents.

ID     datetime               filename 
----------------------------------------
1     2025-01-01 10:00:00     jan 
2     2025-02-01 10:00:00     feb 
3     2025-02-15 10:00:00     feb-2  
4     2025-03-01 10:00:00     mar      
5     2025-04-01 10:00:00     apr 
6     2025-04-15 10:00:00     apr-2

In the above example, if I was to run the query on the 15th March, I would get three docs returned, mar (most recent), apr and apr-2 (future docs)

I can get the desired results from running two queries

SELECT * FROM docs WHERE datetime <= NOW() ORDER BY datetime DESC LIMIT 1
SELECT * FROM docs WHERE datetime > NOW() ORDER BY datetime ASC

but would prefer to use a single query if possible?

Solution

We can use a LEAD() trick here:

WITH cte AS (
    SELECT *, LEAD(datetime, 1, datetime) OVER (ORDER BY datetime) AS datetimelead
    FROM yourTable
)

SELECT ID, datetime, filename
FROM cte
WHERE datetimelead > '2025-03-15'
ORDER BY datetime;