July 6, 2011 at 3:20 am
Hi,
I have a table in the following format -
Create Table myTable(ID Varchar(20),GUID Varchar(20), ServiceDate DateTime, Description Varchar(100))
Composite Key - ID and GUID
INSERT INTO myTable ('ABC','01/02/2011','GUID1','TEST1')
INSERT INTO myTable ('ABC','01/03/2011','GUID2','TEST2')
INSERT INTO myTable ('DEF','01/04/2011','GUID3','TEST3')
INSERT INTO myTable ('GHI','01/05/2011','GUID4','TEST4')
INSERT INTO myTable ('GHI','01/06/2011','GUID5','TEST5')
INSERT INTO myTable ('JKL','01/07/2011','GUID6','TEST6')
INSERT INTO myTable ('MNO','01/08/2011','GUID7','TEST7')
INSERT INTO myTable ('PQR','01/09/2011','GUID8','TEST8')
INSERT INTO myTable ('PQR','01/10/2011','GUID9','TEST9')
INSERT INTO myTable ('STU','01/11/2011','GUID10','TEST10')
As per primary key combination there is not duplciate data in this table. But i am generating a report which shows only ID, ServiceDate and Description.
Hence the result will be duplicate data in report.
Problem - My user wants to delete the duplicate records which have similar "ID". The criterion should be keeping the records with the latest ServiceDate.
For example from the below records -
INSERT INTO myTable ('ABC','01/02/2011','GUID1','TEST1')
INSERT INTO myTable ('ABC','01/03/2011','GUID2','TEST2')
Then report should show -
ABC , 01/03/2011,GUID2,TEST2.
Till that requirement i was using this query to delete duplicate records -
WITH temptable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rownumber,*
FROM myTable
)
SELECT * FROM temptable WHERE rownumber > 1
Could any one please help me to filter the records based on the date range and then delete. Also please reply if any one needs more information on that.
Thanks in Advance.
MJ:cool:
July 6, 2011 at 3:25 am
Change
PARTITION BY ID ORDER BY ID
to
PARTITION BY ID ORDER BY ServiceDate DESC
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537July 6, 2011 at 5:50 am
Hi
Create Table myTable(ID Varchar(20), ServiceDate DateTime,GUID Varchar(20), Description Varchar(100))
--Composite Key - ID and GUID
INSERT INTO myTable VALUES('ABC','01/02/2011','GUID1','TEST1')
INSERT INTO myTable VALUES ('ABC','01/03/2011','GUID2','TEST2')
INSERT INTO myTable VALUES('DEF','01/04/2011','GUID3','TEST3')
INSERT INTO myTable VALUES('GHI','01/05/2011','GUID4','TEST4')
INSERT INTO myTable VALUES('GHI','01/06/2011','GUID5','TEST5')
INSERT INTO myTable VALUES('JKL','01/07/2011','GUID6','TEST6')
INSERT INTO myTable VALUES('MNO','01/08/2011','GUID7','TEST7')
INSERT INTO myTable VALUES('PQR','01/09/2011','GUID8','TEST8')
INSERT INTO myTable VALUES('PQR','01/10/2011','GUID9','TEST9')
INSERT INTO myTable VALUES('STU','01/11/2011','GUID10','TEST10')
select * from myTable
select * from myTable bb where ID in (select id from myTable group by ID) and ServiceDate= (select MAX(ServiceDate) from myTable aa where aa.ID =bb.ID) order by ID
enjoy..:)
July 6, 2011 at 7:04 am
Mark-101232 (7/6/2011)
ChangePARTITION BY ID ORDER BY ID
to
PARTITION BY ID ORDER BY ServiceDate DESC
I'd agree with this suggestion.
Although you'll need to make one more change - You want only the first row in this case, not all the ones greater than rowNumber = 1. Switch it from
WITH temptable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rownumber,*
FROM myTable
)
SELECT * FROM temptable WHERE rownumber > 1
to
WITH temptable AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ServiceDate DESC) AS rownumber,*
FROM myTable
)
SELECT * FROM temptable WHERE rownumber = 1
July 6, 2011 at 8:19 am
Thanks a lot.... Mark and kramaswamy........:-)
MJ:cool:
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply