By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE exams (
student text,
date date
);
INSERT INTO exams VALUES
('aa', '2018-10-10'),
('aa', '2018-10-10'),
('aa', '2018-10-09'),
('bb', '2018-10-10'),
('bb', '2018-10-09'),
('bb', '2018-10-09'),
('cc', '2018-10-10'),
('cc', '2018-10-09');
SELECT * FROM exams;
8 rows affected
| student | date |
|---|---|
| aa | 2018-10-10 |
| aa | 2018-10-10 |
| aa | 2018-10-09 |
| bb | 2018-10-10 |
| bb | 2018-10-09 |
| bb | 2018-10-09 |
| cc | 2018-10-10 |
| cc | 2018-10-09 |
SELECT
*
FROM exams e
JOIN (
SELECT DISTINCT ON (e.student)
*
FROM exams e
ORDER BY e.student, e.date DESC
) s USING (student, date)
GROUP BY e.student, e.date
HAVING COUNT(e.date) >= 2
ORDER BY e.student
| student | date |
|---|---|
| aa | 2018-10-10 |