Solved

Compare and filter data in sql server table

Posted on 2013-06-17
7
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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 500 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

691 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