SQL select rows containing empty field using GROUP BY, HAVING COUNT

Question

What I'm trying to do is very similar to Select first row in each GROUP BY group?

but other than select first row after ORDER BY number DESC, I want to select a row shows up multiple times in database (has changed name in past) AND empty field under Change_Name_to.

For example, I have a table that shows person's name change history and current name.

+--------+--------------+----------------+
| UserID | Current_Name | Change_Name_to |
+--------+--------------+----------------+
|     30 | Name3        |                |
|     30 | Name2        | Name3          |
|     30 | Name1        | Name2          |
|     10 | Name5        |                |
|     20 | Name7        |                |
|     20 | Name6        | Name7          |
+--------+--------------+----------------+

what I want to do here is

+--------+--------------+----------------+
| UserID | Current_Name | Change_Name_to |
+--------+--------------+----------------+
|     30 | Name3        |                |
|     20 | Name7        |                |
+--------+--------------+----------------+

How should I do this?

SELECT *, COUNT(*) FROM `docs` GROUP BY id HAVING COUNT(UserID) > 1

I understand this doesn't work, but something like this is something I wanted to do.

Answer 1

I think you can do what you want using:

select d.*
from docs d
where d.change_name_to is null and
      exists (select 1
              from docs d2
              where d2.userid = d.userid and d2.change_name_to is not null
             );