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.
>Solution :
SELECT *
FROM table
WHERE {needed conditions}
ORDER BY end_date IS NULL DESC, end_date DESC LIMIT 1