0

I'm trying to remove all rows from a database that duplicate an int (named user_id) in order to keep just the first occurrence. Not sure why my attempts didn't work, and would like an explanation of how you solved the problem even more than a solution.

My attempt (and sample data) http://sqlfiddle.com/#!18/9f6fc/5

End goal:

user_id, PAios_AccountId
123      a
223      b
Rilcon42
  • 9,584
  • 18
  • 83
  • 167
  • Yours didn't work because you grouped by both columns and there are no duplicates in the data. But for this to truly work you need to define first. Is it sorted by PAios_AccountId? – Sean Lange May 22 '18 at 14:32
  • You usually (always?) don't want to group by the values you are aggregating since this will make the rows distinct (and you don't have any count > 1 if they are all unique). – Jacob H May 22 '18 at 14:32
  • Questions seeking debugging help ("why isn't this code working?") must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Questions without a clear problem statement are not useful to other readers – Tab Alleman May 22 '18 at 14:39

1 Answers1

3

The easiest way is to use ROW_NUMBER:

WITH cte AS (
 SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY PAios_AccountId) AS rn 
 FROM [User]
)
DELETE FROM cte
WHERE rn <> 1;

DBFiddle Demo

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275