SQL Server Select Query to remove duplicate records and compute a new column

I have a table with the columns below and this table that contains some duplicate Address records.  I need a SQL select query that this extract all the columns below and remove the records that contain duplicate records based on the address column.   I also need this query to compute a new date column for me called lead date.  This is computed by adding 7 days to the RecordingDate column.

Address      
City      
State
Zip
RecordingDate

thanks,
hojohappyAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
Here's a query that will give you row numbers:

SELECT Address, City, State, Zip, 
ROW_NUMBER ( )    OVER ( [ PARTITION BY Address, City, State, Zip ORDER BY RecordingDate DESC) as RowNum
FROM MyTable

Open in new window


Now if there is an additional column, such as an identity column or a GUID it makes it easier to delete. But without that column it gets to be more difficult.
0
 
SreeramConnect With a Mentor Commented:
Hi

You can use the Below statement

RecordingDate should be as an datetime column so that below query will work Or convert RecordingDate to datetime data format in query itself

Query:

Select Distinct(Address),City,state,zip,RecordingDate,((RecordingDate)+7) as Leaddate from TableA
0
 
magarityCommented:
You can get the duplicates rather easily:
select count(1), address, city, state, zip from table group by  address, city, state, zip having count(1) > 1;
then you can delete them with whatever is the primary key. address_id or some such, I assume.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
smiliefaceCommented:
Given that you don't care which of the duplicate records you get, you could use this.
SELECT
   Address,
   MAX(City)  AS City,
   MAX(State) AS State,
   MAX(Zip) AS Zip,
   MAX(RecordingDate) AS RecordingDate,
   MAX(DATEADD(dd, 7, RecordingDate)) AS LeadDate
   FROM <Table Name Here>
   GROUP BY Address

Open in new window

0
 
jogosCommented:
Took Jim .P query to start to find doubles everything with rownum=1 is unique or is an older duplicate.
So I introduced it in an understandable query that first select your results so you can check before you start to delete
select * from
--delete
 MyTable
where id in 
(select x.id 
 
from (
   SELECT id,Address, City, State, Zip, 
   ROW_NUMBER ( )    OVER ( PARTITION BY Address, City, State, Zip 
                                                ORDER BY RecordingDate   DESC) as RowNum
FROM MyTable
) as x
where x.RowNum > 1

Open in new window

0
 
Eugene ZCommented:
what is your sql server version?
0
 
Eugene ZCommented:
you may like to use this method to remove dups and prevent dups in future

How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.