March 25, 2009 at 1:38 am
In 1 of our logging tables i found double records which i want to delete. The problem is that i can't just delete the double records. I only want to delete them when there are succeeding (2 or more) records.
The test case:
CREATE TABLE #History (
ID int identity(1,1)
, IDStatus int
, IDItem int
, DateCreated datetime)
INSERT INTO #History (IDStatus, IDItem, DateCreated)
SELECT 1, 1, '2009-01-01'
UNION ALL
SELECT 1, 2, '2009-01-01'
UNION ALL
SELECT 1, 1, '2009-01-03'
UNION ALL
SELECT 1, 1, '2009-01-02'
UNION ALL
SELECT 1, 2, '2009-01-02'
UNION ALL
SELECT 1, 1, '2009-01-03'
UNION ALL
SELECT 2, 1, '2009-01-04'
UNION ALL
SELECT 1, 1, '2009-01-05'
UNION ALL
SELECT 1, 1, '2009-01-06'
UNION ALL
SELECT 1, 1, '2009-01-07'
The table now contains:
select * from #History
order by IDItem, DateCreated
1112009-01-01 00:00:00.000
4112009-01-02 00:00:00.000
6112009-01-03 00:00:00.000
3112009-01-03 00:00:00.000
7212009-01-04 00:00:00.000
8112009-01-05 00:00:00.000
9112009-01-06 00:00:00.000
10112009-01-07 00:00:00.000
2122009-01-01 00:00:00.000
5122009-01-02 00:00:00.000
Now i want to delete all double succeeding records, after this the result should be:
1112009-01-01 00:00:00.000
7212009-01-04 00:00:00.000
8112009-01-05 00:00:00.000
2122009-01-01 00:00:00.000
I have been trying to solve this with row_number() but i can't figure it out.
March 25, 2009 at 7:16 am
I think that this will do it, (at least the query's results have the same records that you showed as the results that you are looking for), but next time pleas supply more explanations about the logic that you use to determined what records you need to show .
with MyCTE as (
select id, DateCreated, IDStatus, IDItem, row_number() over (order by IDItem, DateCreated) as RowNum
from #history
)
select m1.id, m1.DateCreated, m1.IDStatus, m1.IDItem
from MyCTE m1 left join MyCTE m2 on m2.RowNum+1 = m1.RowNum and m1.IDStatus = m2.IDStatus and m1.IDItem = m2.IDItem
where m2.RowNum is null
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 25, 2009 at 7:18 am
From the expected results, it looks like you want to retain status changes and not delete duplicates
Buzz
March 25, 2009 at 7:19 am
From the expected results, it looks like you want to retain status changes and not delete duplicates
Buzz
March 25, 2009 at 11:50 am
This one will do the trick.
delete from #History where id in (
select h1.id from #history h1 inner join #history h2 on
h1.idstatus=h2.idstatus
and h1.iditem=h2.iditem
and h1.datecreated=h2.datecreated
where h1.id <h2.id)
This will end up leaving the more recent records assuming identity field is populating as such.
Mark G
March 25, 2009 at 11:59 am
scratch my last post....I just realized I misunderstood your goal.
The way I read it:
The key is : idstatus and iditem
and you want to delete duplicate records and records that are within one day of the original and subsequent duplicates?
March 25, 2009 at 12:19 pm
Hi Sander
Try this:
SET NOCOUNT ON
DECLARE @History TABLE (
ID int identity(1,1)
, IDStatus int
, IDItem int
, DateCreated datetime)
INSERT INTO @History (IDStatus, IDItem, DateCreated)
SELECT 1, 1, '2009-01-01'
UNION ALL SELECT 1, 2, '2009-01-01'
UNION ALL SELECT 1, 1, '2009-01-03'
UNION ALL SELECT 1, 1, '2009-01-02'
UNION ALL SELECT 1, 2, '2009-01-02'
UNION ALL SELECT 1, 1, '2009-01-03'
UNION ALL SELECT 2, 1, '2009-01-04'
UNION ALL SELECT 1, 1, '2009-01-05'
UNION ALL SELECT 1, 1, '2009-01-06'
UNION ALL SELECT 1, 1, '2009-01-07'
;WITH hist (id, idstatus, iditem, row_num) AS
(
SELECT ID, IDStatus, IDItem, ROW_NUMBER() OVER (ORDER BY IDItem, DateCreated)
FROM @History
)
--SELECT *
DELETE h
FROM @History h
JOIN hist h1 ON h.ID = h1.id
LEFT JOIN hist h2 ON h1.row_num = h2.row_num + 1 AND h1.idstatus = h2.idstatus AND h1.iditem = h2.iditem
WHERE h2.id IS NOT NULL
SELECT * FROM @History
Greets
Flo
March 26, 2009 at 12:10 am
Florian Reischl (3/25/2009)
Hi SanderTry this:
SET NOCOUNT ON
DECLARE @History TABLE (
ID int identity(1,1)
, IDStatus int
, IDItem int
, DateCreated datetime)
INSERT INTO @History (IDStatus, IDItem, DateCreated)
SELECT 1, 1, '2009-01-01'
UNION ALL SELECT 1, 2, '2009-01-01'
UNION ALL SELECT 1, 1, '2009-01-03'
UNION ALL SELECT 1, 1, '2009-01-02'
UNION ALL SELECT 1, 2, '2009-01-02'
UNION ALL SELECT 1, 1, '2009-01-03'
UNION ALL SELECT 2, 1, '2009-01-04'
UNION ALL SELECT 1, 1, '2009-01-05'
UNION ALL SELECT 1, 1, '2009-01-06'
UNION ALL SELECT 1, 1, '2009-01-07'
;WITH hist (id, idstatus, iditem, row_num) AS
(
SELECT ID, IDStatus, IDItem, ROW_NUMBER() OVER (ORDER BY IDItem, DateCreated)
FROM @History
)
--SELECT *
DELETE h
FROM @History h
JOIN hist h1 ON h.ID = h1.id
LEFT JOIN hist h2 ON h1.row_num = h2.row_num + 1 AND h1.idstatus = h2.idstatus AND h1.iditem = h2.iditem
WHERE h2.id IS NOT NULL
SELECT * FROM @History
Greets
Flo
Damn, it's so simple now that i know the answer, i was thinking of something like this but i couldn't figure it out how to do it exactly.
Thanks, this will do the job 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply