CODEWITHSUNDEEP
sqlsql-serverdelete-row
Mike Brinkman
Mike Brinkman

Reputation: 3

SQL Delete after a different date for each ID

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!

Upvotes: 0

Views: 54

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460228

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.

Upvotes: 1

Related Questions