How to get the first non-NULL value in a group for a given field?

Question

SQL

Let me explain.
For example, we have some SELECT ... GROUP BY.
It is necessary for each group to get the first non-zero value for the given field (not included in the grouping fields). Obviously, some ORDER BY is assumed within the groups.
Let there be a table like this:
client_id massage dttm
1 NULL 2022-01-01 05:12:12
1 hi 2022-01-01 05:12:15
1 I want to fuck 2022-01-01 05:12:18
3 NULL 2022 -01-02 05:12:12
3 NULL 2022-01-02 05:12:15
3 ohhh 2022-01-02 05:12:18
4 nice dick pic! 2022-01-03 05:12:18

Request should return:
client_id massage dttm
1 I want to fuck 2022-01-01 05:12:18
3 ohhh 2022-01-02 05:12:18
4 nice dick pic! 2022-01-03 05:12:18

Answer 1

SELECT DISTINCT 
       client_id,
       FIRST_VALUE(massage) 
           OVER (PARTITION BY client_id 
                 ORDER BY massage IS NULL, dttm) massage
FROM test

If the DBMS does not support direct ORDER BY massage IS NULL, dttm , then ORDER BY CASE WHEN massage IS NULL THEN 1 ELSE 0 END, dttm .
https://dbfiddle.uk/?rdbms=mysql_8.0&rdbms2=sqlser...

Answer 2

Perhaps if you give an example of a complete request with clarifications, it will be easier to help you.
Maybe post-filtering with HAVING will suit you, or maybe you will need to apply the COALESCE function. Not clear without details.