?
Solved

Compare and filter data in sql server table

Posted on 2013-06-17
7
Medium Priority
?
318 Views
Last Modified: 2013-06-17
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
Comment
Question by:peternordberg
  • 3
  • 3
7 Comments
 
LVL 8

Expert Comment

by:Ganapathi
ID: 39252443
Are you trying to filter them in your Select query or while inserting the table itself you want to ignore the duplicates?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39252449
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
 

Author Comment

by:peternordberg
ID: 39252465
I'm going to filter in the select query.

Peter
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

Author Comment

by:peternordberg
ID: 39252467
the unique identifier is customerID, int. I'm sorry I omitted that.

Peter
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39252488
>>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
 

Author Comment

by:peternordberg
ID: 39252647
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 39252685
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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question