SQL Delete after a different date for each ID

Question

I am performing a conversion analysis that only needs to look at activity up to the first conversion for each User_ID.

I can find the time of the first conversion for each user with this query:

select min([Time]), [User_ID]
from [Table_1]
where [Event_Type] = 2 --just a filter for conversions
group by [User_ID]

However, now I want to delete all data from [Table1] for each [User_ID], after their specific min([time]). How do I do this?

Any help is appreciated!

Tim Schmelter · Accepted Answer · 2015-02-27T23:19:12.910

You could use a CTE + ROW_NUMBER:

With CTE AS
(
   SELECT [Time], [User_ID],
          RN = ROW_NUMBER() OVER (Partition By [User_ID] ORDER BY [Time])
   FROM Table1
   WHERE [Event_Type] = 2
)
DELETE FROM CTE WHERE RN > 1

If you don't want to delete all but one record but all with a time that is different to the min-time for each user (in case of ties), use DENSE_RANK instead. Use SELECT * if you want to see what you'll delete.

SQL Delete after a different date for each ID

1 Answers1