Compare dates within the same column-CodePudding

I have the following query:

SELECT title, DATE(end) AS Regresso FROM raddb.AgendaSaidas WHERE title = '763';

Returns the following data:

# title, Regresso
'763', '2023-01-11'
'763', '2023-01-08'
'763', '2023-01-07'
'763', '2023-01-01'

If you notice, there are two dates with a difference of 1 day, this one 2023-01-07 and this one 2023-01-08. I intend to compare these dates and in this situation only return the most recent date. I want it to only return records where the date is different by at least 3 days.

I'm trying this but it doesn't work:

SELECT DISTINCT title, DATE(end) AS Regresso
FROM raddb.AgendaSaidas AS d1, raddb.AgendaSaidas AS d2 
WHERE d1.title = '763' AND d2.title = '763' AND DATE(d1.end) >= (DATE(d2.end)   INTERVAL 3 DAY)

I'll put the data I want it to return as:

# title, Regresso
'763', '2023-01-11'
'763', '2023-01-08'
'763', '2023-01-01'

Example

CodePudding user response：

WITH 
cte1 AS (
    SELECT title, 
           DATE(end) AS Regresso
    FROM raddb.AgendaSaidas 
    WHERE title = '763'
),
cte2 AS (
    SELECT title, 
           Regresso, 
           DATEDIFF(LEAD(Regresso) OVER (ORDER BY Regresso), Regresso) delta
    FROM cte1
)
SELECT title, Regresso
FROM cte2
WHERE delta >= 3 OR delta IS NULL;