I have a table that looks, for example, like this:
| ID | create | status | update |
|---|---|---|---|
| 1 | 2020-09-07 | 0 | 2022-09-07 |
| 2 | 2020-03-25 | 0 | 2022-09-07 |
| 3 | 2021-06-12 | 1 | 2022-09-07 |
| 4 | 2018-10-12 | 1 | 2019-09-07 |
I'm trying to select the row that has status = 1 and the most recent date in create or update. I know based on this answer how to do this with one date field (ie, selecting the most recent create field alone using Max(create)). But how can one choose the most recent of either? What would this query look like?
CodePudding user response：
SELECT ...
FROM a_table_that_looks_for_example_like_this
WHERE status = 1
ORDER BY GREATEST(`create`, `update`) DESC
LIMIT 1