hojohappy
asked on
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,
Address
City
State
Zip
RecordingDate
thanks,
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
what is your sql server version?
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
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/kb/139444
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.