Search code examples
sqlsql-servergroup-bydistinct

SQL: select unique rows

This is a "toy" example of a table that has many columns and 100s of thousands of rows.

I want FILTER OUT any rows containing the same AcctNo, CustomerName and CustomerContact, but KEEP the ID for ONE of the duplicates (so i can access the record later).

  • Example:

    ID  AcctNo  CustomerName  CustomerContact
1   1111    Acme Foods    John Smith
2   1111    Acme Foods    John Smith
3   1111    Acme Foods    Judy Lawson
4   2222    YoyoDyne Inc  Thomas Pynchon
5   2222    YoyoDyne Inc  Thomas Pynchon
<= I want to save IDs 2, 3, and 5

  • Fiddle: https://www.db-fiddle.com/f/bEECHi6XnvKAeXC4Xthrrr/1

Q: What SQL do I need to accomplish this?

Solution 
  • select MAX(ID) as KeepID,AcctNo,CustomerName,CustomerContact 
from test
GROUP BY AcctNo,CustomerName,CustomerContact