By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE articles (`id` INTEGER, `date` TEXT, `headline` VARCHAR(27));
✓
INSERT INTO articles (`id`, `date`, `headline`) VALUES
('1', '2020-01-01', 'This one weird trick...'),
('2', '2020-01-01', 'These two weird tricks...'),
('3', '2020-01-01', 'These fifty weird tricks...'),
('4', '2020-01-02', 'This one crazy trick...'),
('5', '2020-01-02', 'This one odd trick...'),
('6', '2020-01-03', 'These tricks...'),
('7', '2020-01-04', 'These tricks...'),
('8', '2020-01-05', 'These tricks...');
✓
WITH cte(date) AS (
SELECT MIN(date) FROM articles
UNION ALL
SELECT date(date, '+1 days')
FROM cte
WHERE date(date, '+1 days') <= (SELECT MAX(date) FROM articles)
)
SELECT MIN(a.id) id, a.date, a.headline
FROM articles a INNER JOIN cte c
ON c.date = a.date
GROUP BY a.date
| id | date | headline |
|---|---|---|
| 1 | 2020-01-01 | This one weird trick... |
| 4 | 2020-01-02 | This one crazy trick... |
| 6 | 2020-01-03 | These tricks... |
| 7 | 2020-01-04 | These tricks... |
| 8 | 2020-01-05 | These tricks... |
WITH cte(date) AS (
SELECT MIN(date) FROM articles
UNION ALL
SELECT date(date, '+3 days')
FROM cte
WHERE date(date, '+3 days') <= (SELECT MAX(date) FROM articles)
)
SELECT MIN(a.id) id, a.date, a.headline
FROM articles a INNER JOIN cte c
ON c.date = a.date
GROUP BY a.date
| id | date | headline |
|---|---|---|
| 1 | 2020-01-01 | This one weird trick... |
| 7 | 2020-01-04 | These tricks... |