CODEWITHSUNDEEP
sqlsql-server
InTheWorldOfCodingApplications
InTheWorldOfCodingApplications

Reputation: 2616

Select Rows that appear more than once

   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

Upvotes: 5

Views: 5107

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460058

You could use a simple correlated sub-query:

SELECT PersonId, PersonScore
FROM dbo.TableOne t1
WHERE (SELECT COUNT(*) 
       FROM dbo.TableOne t2 
       WHERE t1.PersonId = t2.PersonID) > 1

Upvotes: 2

Tom
Tom

Reputation: 7740

SELECT *
FROM TableOne t1
JOIN (SELECT PersonId
      FROM TableOne
      GROUP BY PersonId
      HAVING COUNT(*) > 1) t2
on t1.PersonId = t2.PersonId

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269553

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;

Upvotes: 5

Related Questions