CODEWITHSUNDEEP
sqlsql-server
BradenA8
BradenA8

Reputation: 119

Remove duplicate rows from query and 'multi-part identifier could not be bound' error

This is my query which is producing duplicate values in Column3.

Select 
D.ID,
E.Date,
D.Column3, 
E.Column4,
D.Column5,
E.Column6, 
From [TABLE_E] As E
Join [TABLE_D] as D ON E.Column3 = D.Column3 
WHERE D.Column5 IN ('Value1', 'Value2') 
Order by DATE desc

When Table_D is updated with one piece of information it will create two rows with the same value in Column3 and most other columns. For the sake of this query, I only want to show one row per value in Column3 and it doesn't matter which one or which table it is from (D or E).

I have tried DISTINCT Column3 which doesn't work and still brings through the exact same amount of rows and using GROUP BY gives me the following error message 'Column 'D.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

So after searching for a while I came across a link where someone was suggested to use 'ROW_NUMBER(), OVER, PARTITION BY and SEQNUM' functions in their query (which I have never used before). I tried to adapt this to mine as: 

Select 
D.ID,
E.Date,
D.Column3, 
E.Column4,
D.Column5,
E.Column6,
FROM [TABLE_E] As E
LEFT JOIN (SELECT D.*, ROW_NUMBER() OVER (PARTITION BY E.Column3 order by E.Date DESC) as SEQNUM 
FROM [TABLE_D] as D) D ON E.Column3 = D.Column3 and 
SEQNUM = 1 
WHERE D.Column5 IN ('Value1', 'Value2') 
GROUP BY E.Column3
ORDER BY E.DATE desc

Now I am receiving 'The multi-part identifier "E.DATE" could not be bound.' as an error message (on the line with the Left Join). I have then done some searching for help with this error, but any answers given don't seem to relate to my code and I can't get them to work.

I would appreciate help with any part of my problem, if there is anyway to just remove the duplicate rows from my first query that would be ideal. But if there is a way to resolve the error that I am receiving with the second query, that would also be great and I can then see if that removes the duplicates.

Upvotes: 0

Views: 207

Answers (1)

Limonka
Limonka

Reputation: 846

Try going this way:

with DistinctD as (
    select max(id) as maxId, Column3 from TABLE_D 
    WHERE Column5 IN ('Value1', 'Value2')
    group by Column3
),
DistinctE as (
    select rn = ROW_NUMBER () OVER (partition by column3 order by date), *
    from TABLE_E
)
Select 
D.ID,
E.Date,
D.Column3, 
E.Column4,
D.Column5,
E.Column6
From DistinctE As E
Join TABLE_D as D ON E.Column3 = D.Column3 
where d.id in (select maxId from DistinctD)
and e.rn = 1
Order by DATE desc;

Upvotes: 0

Related Questions