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.

score 3 · Accepted Answer · answered Aug 06 '18 at 19:03

3

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
             );

answered Aug 06 '18 at 19:03

Gordon Linoff

1,242,037
58
646
786

this was exactly what I was looking for. Thanks a lot, it was very clear and easy to understand as soon as I saw your query. – huza Aug 06 '18 at 19:20

score 2 · Answer 2 · answered Aug 06 '18 at 19:20

2

Based on that sample data, all you need is

select * 
from docs d
where d.change_name_to is null
and UserID in (select UserID from docs group by UserID having count(UserID) > 1)

answered Aug 06 '18 at 19:20

S3S

24,809
5
26
45

score 0 · Answer 3 · answered Aug 06 '18 at 19:48

0

Check on the next:

SELECT
  l.UserID, l.Current_Name, l.Change_Name_to
FROM @docs l JOIN @docs r
  ON l.UserID = r.UserID AND
     l.Current_Name = r.Change_Name_to
WHERE l.Change_Name_to IS NULL AND
      r.Change_Name_to IS NOT NULL;

answered Aug 06 '18 at 19:48

Andrei Odegov

2,925
2
15
21

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

3 Answers3