MySQL query to get only one record: either when end_date is null or max(end

I have the following table (employee_organization):

ID	employee_id	organization_id	start_date	end_date
1	77	16	2021-01-01	2021-06-30
2	11	23	2020-01-01	2021-05-27
3	77	16	2021-08-01	2021-08-31
4	77	16	2021-09-01	NULL

I need a query to filter out records where employee_id = 77, organization_id = 16 and end_date is null. If no matching row has been found, than return a row with max(end_date). So, in the above sample table only row with id=4 should be returned.

CodePudding user response：

SELECT *
FROM table
WHERE {needed conditions}
ORDER BY end_date IS NULL DESC, end_date DESC LIMIT 1

CodePudding user response：

A generic solution is to use correlated subquery that finds greatest date per employee (nulls first):

select *
from t
where end_date <=> (
  select end_date
  from t as x
  where x.employee_id = t.employee_id
  order by end_date IS NOT NULL, end_date desc
  limit 1
)