Get the row with a NULL column from GROUP BY-CodePudding

How do I get the row which has a null value in address_1 from below query.

SELECT id, address_1, address_2, address_3 FROM table GROUP BY address_2, address_3 HAVING COUNT(id) > 1;

There is only one row which has a null value in address_1 in all groupings.

id    address_1    address_2    address_3
-----------------------------------
1    7 Orange     High County  CA 10020
2                 High County  CA 10020
3    20 Orange    High County  CA 10020
4                 True Avenue  VA 24019
5    100 Apple    True Avenue  VA 24019
6    123 Apple    True Avenue  VA 24019
7                 Long Island  NY 10000
...

I would like to get the id of the row 2 and 4 (to update or delete it accordingly).

CodePudding user response：

SELECT id, address_1, address_2, address_3 
FROM table 
WHERE address_1 IS NULL 
group by address_2,address_3;

if There is only one row that has a null value in address_1 in each group then there is no need to group by.

is that what you need? or am I didn't understand you?

CodePudding user response：

For this sample data you can use conditional aggregation:

SELECT MAX(CASE WHEN address_1 IS NULL THEN id END) id
FROM tablename
GROUP BY address_2, address_3
HAVING COUNT(address_1) > 0 AND COUNT(address_1) < COUNT(*);

See the demo.

CodePudding user response：

To find such id values, you may use:

SELECT id
FROM yourTable t1
WHERE address_1 IS NULL AND
      EXISTS (SELECT 1 FROM yourTable t2
              WHERE t2.address_2 = t1.address_2 AND
                    t2.address_3 = t1.address_3 AND
                    t2.id <> t1.id AND
                    t2.id IS NOT NULL);

Demo