CODEWITHSUNDEEP
sqlsql-servergroup-byhaving
huza
huza

Reputation: 107

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

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.

Upvotes: 3

Views: 90

Answers (3)

Andrei Odegov
Andrei Odegov

Reputation: 3429

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;

Upvotes: 0

S3S
S3S

Reputation: 25112

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)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

Upvotes: 3

Related Questions