CODEWITHSUNDEEP
sql-server
dwirony
dwirony

Reputation: 5450

Using ROW_NUMBER() to remove duplicates in SQL server

My current query returns too many lines per Subject_ID, so I want to use ROW_NUMBER() to limit the resulting set to 1 line per Subject_ID. I've added this line to my SELECT statement:

, ROW_NUMBER() over(partition by CS.Subject_ID order by CS.Subject_ID) rn

But when I try to put WHERE rn = 1 anywhere in the FROM statement, I get the error:

Incorrect syntax near the keyword 'WHERE'

And when I try to change it to AND rn = 1 (and add it on to another AND/OR line) I get the error:

Invalid column name 'rn'

So my first question is: When I add a field to my SELECT statement using that ROW_NUMBER() line, what table does this column belong to? Do I need to append it to something like Table.rn? My second question is where should I put this rn = 1 line and how should I write it in?

Full query:

SELECT
    Groups.Group_Name
,   CT.Created
,   CT.Subject_Id
INTO #temp
 FROM SubjectZ_Task CT
                                                INNER JOIN 
                                                SubjectZ_Task_Users On CT.SubjectZ_Task_Id = SubjectZ_Task_Users.SubjectZ_Task_Id 
                                                INNER JOIN 
    Groups ON Groups.Group_ID = SubjectZ_Task_Users.Group_Reference
    WHERE Group_Name LIKE 'Team 1'
                                                        AND CT.Created >= '1/1/2019' AND CT.Created < DATEADD(Day,1,'12/31/2019')
GROUP BY Groups.group_name, CT.Created, CT.Subject_ID
                                                SELECT 
    CT.Group_Name
,   CT.Created
,   CS.Topic_Start_Date
,   CS.Subject_ID
,   P.FirstName
,   P.LastName
,   CS.Subject_Match_ID
,   SubjectX.Firstname AS SubjectX_firstname
,   CS.SubjectY
,   AEC.AEC AS Max_AEC
,   SubjectX.Email_id As SubjectX_Email
,   Phone.Phone
,   ROW_NUMBER() over(partition by CS.Subject_ID order by CS.Subject_ID) rn
 FROM #temp CT
    LEFT JOIN QE_Topic_Summary CS ON CS.Subject_ID = CT.Subject_Id
                                                                            AND (Topic_Status LIKE 'In Progress'
                                                                            OR   Topic_Status LIKE 'Pending')
                                                                            AND  CS.Topic_Start_Date >= DATEADD(Day,-60,CT.Created) AND CS.Topic_Start_Date <= DATEADD(Day,60,CT.Created)
    INNER JOIN Subjects P ON P.Subject_ID = CS.Subject_ID
    LEFT JOIN Subjects SubjectX ON SubjectX.Subject_ID = CS.SubjectX_ID
    LEFT JOIN QE_TB_MAX_AEC AEC ON AEC.Subject_ID = CS.Subject_ID
    INNER JOIN Subject_Identifiers PI ON PI.Subject_ID = P.Subject_ID
    LEFT JOIN Subject_Identifiers PIP ON PIP.Subject_ID = SubjectX.Subject_ID
    LEFT JOIN Subject_Phone Phone On Phone.Subject_ID = P.Subject_ID WHERE Phone.Voice = 1
    drop table #temp

Upvotes: 0

Views: 52

Answers (1)

BJones
BJones

Reputation: 2460

I don't see a reference to rn in your WHERE clause, but my guess is that you need to wrap it in another query like so:

SELECT *
FROM(
    SELECT 
        CT.Group_Name
    ,   CT.Created
    ,   CS.Topic_Start_Date
    ,   CS.Subject_ID
    ,   P.FirstName
    ,   P.LastName
    ,   CS.Subject_Match_ID
    ,   SubjectX.Firstname AS SubjectX_firstname
    ,   CS.SubjectY
    ,   AEC.AEC AS Max_AEC
    ,   SubjectX.Email_id As SubjectX_Email
    ,   Phone.Phone
    ,   ROW_NUMBER() over(partition by CS.Subject_ID order by CS.Subject_ID) rn
     FROM #temp CT
        LEFT JOIN QE_Topic_Summary CS ON CS.Subject_ID = CT.Subject_Id
                AND (Topic_Status LIKE 'In Progress'
                OR   Topic_Status LIKE 'Pending')
                AND  CS.Topic_Start_Date >= DATEADD(Day,-60,CT.Created) AND CS.Topic_Start_Date <= DATEADD(Day,60,CT.Created)
        INNER JOIN Subjects P ON P.Subject_ID = CS.Subject_ID
        LEFT JOIN Subjects SubjectX ON SubjectX.Subject_ID = CS.SubjectX_ID
        LEFT JOIN QE_TB_MAX_AEC AEC ON AEC.Subject_ID = CS.Subject_ID
        INNER JOIN Subject_Identifiers PI ON PI.Subject_ID = P.Subject_ID
        LEFT JOIN Subject_Identifiers PIP ON PIP.Subject_ID = SubjectX.Subject_ID
        LEFT JOIN Subject_Phone Phone On Phone.Subject_ID = P.Subject_ID 
    WHERE Phone.Voice = 1
)t
WHERE t.rn = 1

Upvotes: 1

Related Questions