CODEWITHSUNDEEP
sql
Kane
Kane

Reputation: 43

SQL - Only Return Row If Column Does Not Have Same Value

I'm running into a problem where I'm trying to return rows that has only 1 value match in a column. For example:

CREATE TABLE TEST
(
    ID INT PRIMARY KEY,
    FIRSTNAME VARCHAR(30) NOT NULL,
    LASTNAME VARCHAR(30) NOT NULL,
    EMAIL VARCHAR(50),
    PHONENUMBER VARCHAR(50)
    STAMP DATETIME
)

INSERT INTO TEST VALUES (1, 'Tom', 'Lew', '[email protected]', '11111111111', '2019-08-23 15:12:06.807')
INSERT INTO TEST VALUES (2, 'Tom', 'Lew', '[email protected]', '22222222222', '2019-08-27 15:12:06.807')
INSERT INTO TEST VALUES (3, 'Jack', 'Stan', '[email protected]', '3333333333', '2019-08-03 15:12:06.807')
INSERT INTO TEST VALUES (4, 'John', 'Doe', '[email protected]',  '44444444444', '2019-08-13 15:12:06.807')
INSERT INTO TEST VALUES (5, 'Peter', 'Griffin', '[email protected]', '55555555555', '2019-07-23 15:12:06.807')
INSERT INTO TEST VALUES (6, 'Homer', 'Simpson', '[email protected]', '66666666666', 2019-08-23 15:12:06.807')

SELECT FirstName, LastName, Email, PhoneNumber
FROM TEST GROUP BY FirstName, LastName, Email, PhoneNumber
HAVING COUNT (FirstName) <= 1

So the result is supposed return everything except for 'Tom' because 'Tom' has more than 1 value matches in the First Name column. However, it's returning everything.

If I do a SELECT FirstName, LastName, Email and GROUP BY FirstName, LastName, Email, it comes back correctly.

Upvotes: 1

Views: 3044

Answers (4)

Ashwik
Ashwik

Reputation: 61

I will exclude the duplicate record using ROW_NUMBER():

SELECT * FROM(
              SELECT firstname, lastname, email, phonenumber,
              ROW_NUMBER() OVER(PARTITION BY firstname ORDER BY firstname) AS rn
              FROM test
            )
 WHERE rn=1;

Hope this helps.

Upvotes: 0

forpas
forpas

Reputation: 164054

Since you check for unique FirstNames you should only GROUP BY FirstName and aggregate on the other columns with MAX() or MIN() (it does not make any difference because the condition in the HAVING clause makes sure that only 1 value of these columns exists):

SELECT FirstName, 
  MAX(LastName) LastName, MAX(Email) Email, MAX(PhoneNumber) PhoneNumber
FROM TEST 
GROUP BY FirstName
HAVING COUNT(*) = 1

See the demo.
Results:

| FirstName | LastName | Email                | PhoneNumber |
| --------- | -------- | -------------------- | ----------- |
| Homer     | Simpson  | [email protected] | 66666666666 |
| Jack      | Stan     | [email protected]    | 3333333333  |
| John      | Doe      | [email protected]     | 44444444444 |
| Peter     | Griffin  | [email protected] | 55555555555 |

Upvotes: 0

Ayus Mohanty
Ayus Mohanty

Reputation: 390

The problem with your select query is two toms' don't have the same phonenumber. If you are going to include the phonenumber in the GROUP BY clause it will give you 2 unique rows for both the toms. You need to decide your unique composite columns for GROUP BY.

If you are only concerned about FirstName your query will be like this.

SELECT FirstName, LastName, Email, PhoneNumber
FROM TEST GROUP BY FirstName
HAVING COUNT (FirstName) <= 1

If you are taking LastName and Email into GROUP BY, then:

SELECT FirstName, LastName, Email, PhoneNumber
FROM TEST GROUP BY FirstName, LastName, Email
HAVING COUNT (FirstName) <= 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can use not exists. If you only care about the first name:

select t.*
from test t
where not exists (select 1
                  from t t2
                  where t2.firstname = t.firstname and
                        t2.id <> t.id
                 );

Upvotes: 6

Related Questions