CODEWITHSUNDEEP
sqlsql-serverssmsrow-number
FooBun
FooBun

Reputation: 99

Trying to capture all of the Row_Numbers with Rows > 1

I'm sure this is actually a simple fix (it just sounds like it would be) but my brain cannot wrap around on how to get this to work.

I'm currently using this query

Select 
    m.memberfirstname+' '+m.memberlastname [Name],
    m.MemberDOB, 
    s.GroupID, 
    c.PaperID, 
    cr.PaperScanningStatusID, 
    ROW_NUMBER() OVER(PARTITION BY m.memberfirstname+' '+m.memberlastname,m.MemberDOB,s.GroupID ORDER BY m.MemberDOB) rn
From 
    Papers c, 
    Members m, 
    Sites s, 
    PaperRetrieval cr
Where 
    c.SiteID=s.SiteID
    and c.MemberID=m.MemberID
    and c.PaperID=cr.PaperID
    and cr.PaperScanningStatusID not in (100)

And the rn column works fine and great. Now I need to capture ALL the rows that have an rn count higher than one.

The thing is I would usually write something like rn >= 2, however, I want to capture that first row too. Not just 2 and up.

Not sure how I can capture that first row without capturing the other first rows that don't have a count higher than 1.

Upvotes: 0

Views: 71

Answers (1)

gunvant.k
gunvant.k

Reputation: 1086

you can have one more column that gives you count,

COUNT(*) OVER(PARTITION BY m.memberfirstname+' '+m.memberlastname,m.MemberDOB,s.GroupID ORDER BY m.MemberDOB) CountNo

and then CountNo > 1

Upvotes: 2

Related Questions