Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

Compare and filter data in sql server table

Hi,

I have this table in sql server with the name address looking like this:
firstname, string
lastname, string
address, string
zip, string,
city, string

The table consists of data from different databases and I know there are duplicate addresses in there.

I would like to compare city, zip and lastname, so that those that have more than one instance in the table are left out (they are only shown one time).

How can I achieve this?

Thanks for help!

Peter
0
peternordberg
Asked:
peternordberg
  • 3
  • 3
1 Solution
 
GanapathiCommented:
Are you trying to filter them in your Select query or while inserting the table itself you want to ignore the duplicates?
0
 
PortletPaulCommented:
do you have a unique identifier for every row? (in addition to the columns already listed)

-- e.g. if one did this to locate the "duplicates"
select  city, zip,  lastname, count(*)
from YourTable
group by  city, zip,  lastname
having count(*) > 1

i.e. is there some existing field to help identify which of the duplicated records to retain?
0
 
peternordbergAuthor Commented:
I'm going to filter in the select query.

Peter
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
peternordbergAuthor Commented:
the unique identifier is customerID, int. I'm sorry I omitted that.

Peter
0
 
PortletPaulCommented:
>>I'm going to filter in the select query
is this something like what you are looking for?

select  city, zip,  lastname,  min(customerID) as customerID
from YourTable
group by  city, zip,  lastname

by the way, what happens if a family (same city, same zip, different homes) each become a customer?
0
 
peternordbergAuthor Commented:
Thanks for answer. It's almost what I want.

Say that I would like to add firstname and address in my select statement, but I don't want to group by them since then the filter will also apply to those columns. How can I do that?

Peter
0
 
PortletPaulCommented:
try this, it should be close
select
    customerID
  , city
  , zip
  , lastname
  , firstname
  , address
  from (
            select
                customerID
              , city
              , zip
              , lastname
              , firstname
              , address
              , row_number() over ( partition by city, zip, lastname 
                                    order by customerID
                                   ) as row_ref
            from YourTable
       ) as derived
where row_ref = 1

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now