TableOne
PersonId PersonScore
1 10
1 20
2 99
2 40
3 45
I need to fetch only those rows where PersonId appears More than Once e.g. Following is the resultset i want to achieve
PersonId PersonScore
1 10
1 20
2 99
2 40
i am using cte
;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY i.PersonId ORDER BY i.PersonId) AS [Num]
FROM TableOne i
)
SELECT *
FROM cte
WHERE cte.Num > 1
The problem is it removes the extra rows. It romoves the first instance of any PersonId. Can anyone suggest a solution please
You want to use count(*)
as a window function, rather than row_number()
:
select t.PersonId, t.PersonScore
from (select t.*, count(*) over (partition by PersonId) as cnt
from TableOne t
) t
where cnt > 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With