How to remove the duplicate records with the old timestamp.
-
Hi, How to remove the duplicate records with the old timestamp. Below is the table, contains 2 columns Bugdate and timestamp. I wanted to remove the duplicate records with the old time stamp. ie first three records. It would be pleasure if any one send me the query. BugDate---- Timestamp 4/14/2009 12/8/2009 12:54.611 4/19/2009 12/8/2009 12:54.611 4/26/2009 12/8/2009 12:54.611 4/14/2009 12/8/2009 12:58.623 4/19/2009 12/8/2009 12:58.623 4/26/2009 12/8/2009 12:58.623 Thanks.
-
Hi, How to remove the duplicate records with the old timestamp. Below is the table, contains 2 columns Bugdate and timestamp. I wanted to remove the duplicate records with the old time stamp. ie first three records. It would be pleasure if any one send me the query. BugDate---- Timestamp 4/14/2009 12/8/2009 12:54.611 4/19/2009 12/8/2009 12:54.611 4/26/2009 12/8/2009 12:54.611 4/14/2009 12/8/2009 12:58.623 4/19/2009 12/8/2009 12:58.623 4/26/2009 12/8/2009 12:58.623 Thanks.
Some threads below, people have told how t osearch duplicate records from a table. Check that out. Once you have the records, just find the ones with older timestamp and delete them. I believe it should be a mere customization of the same query.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
-
Some threads below, people have told how t osearch duplicate records from a table. Check that out. Once you have the records, just find the ones with older timestamp and delete them. I believe it should be a mere customization of the same query.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
I know the logic for how to get duplicate rows but I need to delete them. Below is the table and my logic for getting duplicate records with old timestamp. But I need to delete these records. Can you help by doing little modification in my code. ProductID BugDate Bug Timestamp 111 2009-04-12 1 2009-12-08 12:18:07.453 111 2009-04-14 2 2009-12-08 12:18:07.453 111 2009-04-19 1 2009-12-08 12:18:07.453 111 2009-04-12 1 2009-12-08 12:29:07.453 111 2009-04-14 3 2009-12-08 12:29:07.453 111 2009-04-19 2 2009-12-08 12:29:07.453 SELECT [Product ID],BugDate,Bug,[Timestamp] FROM BugsDB_DefectVolume, ( SELECT [Product ID] AS B, MIN([Timestamp]) AS S FROM BugsDB_DefectVolume GROUP BY [Product ID]) X WHERE [Timestamp] = X.S and [Product ID]='111'
-
Hi, How to remove the duplicate records with the old timestamp. Below is the table, contains 2 columns Bugdate and timestamp. I wanted to remove the duplicate records with the old time stamp. ie first three records. It would be pleasure if any one send me the query. BugDate---- Timestamp 4/14/2009 12/8/2009 12:54.611 4/19/2009 12/8/2009 12:54.611 4/26/2009 12/8/2009 12:54.611 4/14/2009 12/8/2009 12:58.623 4/19/2009 12/8/2009 12:58.623 4/26/2009 12/8/2009 12:58.623 Thanks.
Try this. First I am giving a sample demo and then I will modify your query Sample demo
declare @t table(bugdate varchar(10),timestamps datetime)
insert into @t
select '4/14/2009','12/8/2009 12:54.611' union all
select '4/19/2009', '12/8/2009 12:54.611' union all
select '4/26/2009', '12/8/2009 12:54.611' union all
select '4/14/2009','12/8/2009 12:58.623' union all
select '4/19/2009','12/8/2009 12:58.623' union all
select '4/26/2009','12/8/2009 12:58.623'Query:
delete from @t where timestamps in(
select MIN(timestamps) from @t
group by bugdate
having (COUNT(timestamps)>1))select * from @t
Output:
bugdate timestamps
4/14/2009 2009-12-08 12:58:00.623
4/19/2009 2009-12-08 12:58:00.623
4/26/2009 2009-12-08 12:58:00.623Coming to your code: Try this
delete from BugsDB_DefectVolume where [Timestamp] in(
select [Timestamp] from (
SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
SELECT [Product ID] AS B, MIN([Timestamp]) AS S
FROM BugsDB_DefectVolume
GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111' )X)YHope this helps :)
Niladri Biswas
-
Try this. First I am giving a sample demo and then I will modify your query Sample demo
declare @t table(bugdate varchar(10),timestamps datetime)
insert into @t
select '4/14/2009','12/8/2009 12:54.611' union all
select '4/19/2009', '12/8/2009 12:54.611' union all
select '4/26/2009', '12/8/2009 12:54.611' union all
select '4/14/2009','12/8/2009 12:58.623' union all
select '4/19/2009','12/8/2009 12:58.623' union all
select '4/26/2009','12/8/2009 12:58.623'Query:
delete from @t where timestamps in(
select MIN(timestamps) from @t
group by bugdate
having (COUNT(timestamps)>1))select * from @t
Output:
bugdate timestamps
4/14/2009 2009-12-08 12:58:00.623
4/19/2009 2009-12-08 12:58:00.623
4/26/2009 2009-12-08 12:58:00.623Coming to your code: Try this
delete from BugsDB_DefectVolume where [Timestamp] in(
select [Timestamp] from (
SELECT [Product ID],BugDate,Bug,[Timestamp]
FROM BugsDB_DefectVolume, (
SELECT [Product ID] AS B, MIN([Timestamp]) AS S
FROM BugsDB_DefectVolume
GROUP BY [Product ID]) X
WHERE [Timestamp] = X.S and [Product ID]='111' )X)YHope this helps :)
Niladri Biswas
-
but have one problem. if there are duplicate records that first time its working fine but when we execute second time all records were deleted from the table. I think we need to put a check some where in the logic.
I guess, you can do 1 thing. Put a check like if the timestamp field is not equal to getdate() like select case when (timestamp = getdate()) then call the delete operation end as checking Hope this might help you :)
Niladri Biswas
-
I guess, you can do 1 thing. Put a check like if the timestamp field is not equal to getdate() like select case when (timestamp = getdate()) then call the delete operation end as checking Hope this might help you :)
Niladri Biswas
it will not work out. but the example and query you sent in the previous reply is working fine even we executed query many times. delete from @t where timestamps in(select MIN(timestamps) from @tgroup by bugdate having (COUNT(timestamps)>1)).. As you have used having clause I think we need to put the same thing in my query. I tired but getting syntax errors... Can you think once again...
-
it will not work out. but the example and query you sent in the previous reply is working fine even we executed query many times. delete from @t where timestamps in(select MIN(timestamps) from @tgroup by bugdate having (COUNT(timestamps)>1)).. As you have used having clause I think we need to put the same thing in my query. I tired but getting syntax errors... Can you think once again...