MySQL how to select the first non null value in group by

Question

I have this table

ID  user    test
-----------------
1   john    aaaaa
2   john    aaaaa
3   john    bbbbb
4   john    NULL
5   john    ddddd
6   sonya   NULL
7   sonya   cccccc
8   sonya   dddddd
9   sonya   aaaaaa

And I need to write a query to extract the first non null value of test for each user

if I do a group by

SELECT
    user,
    test,
FROM table
GROUP BY user

I obtain this resultset

user    test
-----------------
john    aaaaa
sonya   NULL

but sonya has a null value, I would like to obtain this:

user    test
-----------------
john    aaaaa
sonya   cccccc

Now, I know that there are ways to obtain sums, count, max, min, etc. from a group by, but I wish to know if there are any ways to get the first non null value

anyone can help?

Answer 1

Try this query

select id, user, test
from T
where id in (
     select min(id) 
     from T as tin 
     where tin.user = T.user and tin.test is not null 
     group by tin.user
  )