title: Select to delete every entry other then the first one
body: <p>I have a Delete query which selects every entry which is older than 7 days exepts the first one.</p>
  
  <p>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.</p>
  
  <p>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... </p>
  
  <p>Can u help me make it faster?</p>
  
  <pre><code>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 &lt; 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(*) &gt; 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 &lt; tab1.MAXDATE)
  </code></pre>