COALESCE for no rows returned

Question

I am using COALESCE to return first non-null values. In below query I am trying to get data for that day of the week which has latest data / or some data (Starting Sunday - Monday). So Let's say if Sunday, Tuesday, Monday has data it should return me Sunday data. If all days have data , it should return me Sunday (7) data. But below query does not return anything. May be because the value returned is zero rows instead of null ??

 SELECT *
        FROM
    status_summary
    WHERE COALESCE(("day_of_week"(Datetime) = 7),("day_of_week"(Datetime) = 6),
    ("day_of_week"(Datetime) = 5),("day_of_week"(Datetime) = 4),
    ("day_of_week"(Datetime) = 3),("day_of_week"(Datetime) = 2),
    ("day_of_week"(Datetime) = 1))

When i run individual query like below, it returns me data.

SELECT *
            FROM
        status_summary
        WHERE COALESCE(("day_of_week"(Datetime) = 5))

OR

   SELECT *
                FROM
            status_summary
            WHERE COALESCE(("day_of_week"(Datetime) = 4))

The syntax is in presto (AWS Athena) but I don't mind sql syntax as well.

SQL : datename(day,MyDate) = 'Sunday'

Answer 1

You'll need to have the sub-select pick the day of week, something like:

SELECT *
FROM status_summary
WHERE day_of_week(Datetime) = (SELECT MAX(day_of_week(Datetime)) FROM status_summary)