how do I query sql for a latest record date but if the record has a message return that reocrd

Question

From this example table I need row 2 and 3.

Answer 1

I understand that you want the latest non-empty message per compID, or the latest message if all messages are empty.

If so, you can use window functions:

select id, compID, date, message
from (
    select 
        t.*,
        row_number() over(
            partition by compID 
            order by case when message is not null then 0 else 1 end, date desc
        ) rn
    from mytable t
) t
where rn = 1
order by id

Demo on DB Fiddle:

id | compID | date       | message    
-: | :----- | :--------- | :----------
 2 | B      | 2020-03-27 | null       
 3 | A      | 2020-03-22 | Hello World