enigma6205 enigma6205 - 6 months ago 10
SQL Question

Return only unique rows form a Table

I have a table with 4 columns and 7 rows.

this is my table sample

This table contains 1 customer with the same ID same

LNAME
and
FNAME
.

Also the table has 2 customers with the same ID, but different
LNAME
or
FNAME
.

That is the sales reps input error. Ideally my table should have only 2 rows (Row with ID_pk 3 and 7)

[![UNIQUE CUSTOMERS][3]][3]

enter image description here

I need to have the following result-sets from the above table:


  1. All unique rows by all the four columns (Row with ID_pk 3 and 7). (excluding case # 3 listed below)

  2. All duplicates by all the four columns (Row with ID_pk 3 and 8).

  3. All duplicates by Customer_ID but with not matching LNAME and/or FNAME (Row with ID_pk 1, 2, 4 and 5) (these rows have to be sent back to sales reps for validation.)


Answer

Doing stuff this like relies heavily on nested queries, the GROUP BY clause, and the COUNT function.

Part 1 - Unique rows

This query will show you all the rows where the customer ID has matching data.

SELECT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers WHERE Customer_ID IN (
    SELECT Customer_ID FROM (
        SELECT DISTINCT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers
        GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
    ) Customers
    GROUP BY Customer_ID
    HAVING COUNT(Customer_ID) = 1
)
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME

Part 2 - Duplicates

This query will show you all the rows that have the same data entered more than once.

SELECT Customer_ID, Customer_FNAME, Customer_LNAME
FROM dbo.customers
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
HAVING COUNT(Customer_ID) > 1

Part 3 - Mismatched Data

This query is basically the same as the first, just looking for a different COUNT value.

SELECT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers WHERE Customer_ID IN (
    SELECT Customer_ID FROM (
        SELECT DISTINCT Customer_ID, Customer_FNAME, Customer_LNAME FROM dbo.customers
        GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
    ) Customers
    GROUP BY Customer_ID
    HAVING COUNT(Customer_ID) > 1
)
GROUP BY Customer_ID, Customer_FNAME, Customer_LNAME
Comments