Filtering down specific criteria for all rows and return only one row for each column with the same value
Must be missing something, but isn't this simply what you want?

SELECT house_lease_id, MIN(date_start)
FROM house_lease_terms
WHERE date_end &gt; NOW() OR date_end IS NULL
GROUP BY house_lease_id


The earliest term that hasn't ended yet.