By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE TABLE1
(
REFERENCE varchar(20),
PROPERTYADDRESS varchar(20),
DATE datetime,
RATING int
);
insert into TABLE1
values
('adef', 'PROPERTY12', '2022-12-08', 70),
('pout', 'PROPERTY12', '2022-12-16', 90),
('mhef', 'PROPERTY17', '2023-01-08', 25),
('jygh', 'PROPERTY17', '2023-01-09', 70),
('boyt', 'PROPERTY22', '2022-10-27', 25),
('qepl', 'PROPERTY22', '2022-10-05', 70);
Records: 6 Duplicates: 0 Warnings: 0
SELECT mt.PROPERTYADDRESS
FROM TABLE1 mt
INNER JOIN
(
SELECT PROPERTYADDRESS, MIN(Date) AS MinDate
FROM TABLE1
GROUP BY PROPERTYADDRESS
) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MinDate
WHERE RATING <= 50
AND mt.PROPERTYADDRESS in (
SELECT mt.PROPERTYADDRESS
FROM TABLE1 mt
INNER JOIN
(
SELECT PROPERTYADDRESS, MAX(Date) AS MaxDate
FROM TABLE1
GROUP BY PROPERTYADDRESS
) t ON mt.PROPERTYADDRESS = t.PROPERTYADDRESS AND mt.DATE = t.MaxDate
WHERE RATING >= 70
);
| PROPERTYADDRESS |
|---|
| PROPERTY17 |