CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Rene Koch
Rene Koch

Reputation: 337

Select to delete every entry other then the first one

I have a Delete query which selects every entry which is older than 7 days exepts the first one.

That means, at the moment: I find every row, grouped by 4 given values, which has more then 1 value and remember the highest date of a timestamp - and then join that to itself to found out the IDs of that to delete them.

The join to itself is huge and very time consuming. On all my data this delete reuqest needs about 30 minutes to finish even after index-magic happened before and done every single day...

Can u help me make it faster?

DELETE FROM [D_PROJEKTE].[dbo].[T_PROTOKOLLE]
WHERE f_index in 
(
  SELECT tab2.f_index FROM 
    (SELECT PROT1.F_ID, PROT1.[F_GERAETE_ADR], PROT1.[F_OBJNR], 
           PROT1.[F_SI] , max(f_datum) as MAXDATE, count(*) as OCCURES
           FROM [D_PROJEKTE].[dbo].[T_PROTOKOLLE] PROT1
           WHERE PROT1.f_datum < dateadd(day, -7, GETDATE())
           and f_id = 9998 -- was älter als 7 Tage, drüber wird nicht gelöscht  
     GROUP BY PROT1.[F_ID]
           ,PROT1.[F_GERAETE_ADR]
           ,PROT1.[F_OBJNR]
           ,PROT1.[F_SI]
     HAVING count(*) > 1 -- was nur einmal vorkommt wird nicht gelöscht 
   ) tab1 
join
    (SELECT 
         F_INDEX, F_ID, F_GERAETE_ADR, F_OBJNR, F_SI, F_DATUM
     FROM 
         [D_PROJEKTE].[dbo].[T_PROTOKOLLE] 
     WHERE 
         f_id = 9998) tab2 ON tab1.F_ID = tab2.F_ID 
                           AND tab1.F_GERAETE_ADR = tab2.F_GERAETE_ADR 
                           AND tab1.F_OBJNR = tab2.F_OBJNR 
                           AND tab1.F_SI = tab2.F_SI
                           AND tab2.f_datum < tab1.MAXDATE)

Upvotes: 1

Views: 80

Answers (1)

M.Ali
M.Ali

Reputation: 69524

;WITH X AS 
(
SELECT * 
      , ROW_NUMBER() OVER (PARTITION BY 
                             F_ID
                          , [F_GERAETE_ADR]
                          , [F_OBJNR]
                          , [F_SI]     ORDER BY f_datum DESC) as rn
FROM [D_PROJEKTE].[dbo].[T_PROTOKOLLE] 
WHERE f_datum < dateadd(day, -7, GETDATE()) 
)
DELETE FROM X where rn > 1

Upvotes: 3

Related Questions