how i can use Distinct in Query with INNER JOIN

Question

I have a query from my products and factors and factor items for show them.

In this query I'm showing a report from factor for user.

My query is :

WITH CTE AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY FactoriTems.datesave ASC) AS rn,
        FactoriTems.code, FactoriTems.replacement, 
        FactoriTems.suggcode, Factors.dateexport,
        Productions.descriptions, 
        FactoriTems.countt, FactoriTems.price, 
        FactoriTems.countt * FactoriTems.price AS 'total', 
        Productions.country             
    FROM 
        Productions 
    INNER JOIN 
        FactoriTems ON Productions.code = FactoriTems.code
    INNER JOIN 
        Factors ON Factors.gid = FactoriTems.gid
    WHERE 
        (FactoriTems.gid = @gid)
) 
SELECT * 
FROM CTE 
ORDER BY rn

This query is OK, but there is a problem, in Productions table some category have two or three product with one code, and when this codes are in factoritems table show two rows in my result set!

Result is :

rn	code	dateexport	descriptions	countt	price	total
1	aaa	12/24/2021	...	100	2	200
2	bbb	12/24/2021	...	200	3	600
3	ccc	12/24/2021	...	100	2	200
4	ddd	12/24/2021	...	200	3	600
5	ddd	12/24/2021	...	100	2	200
6	eee	12/24/2021	...	200	3	600

Now how can I show only one row for 'ddd' product code?

I tried to use DISTINCT but I got an error.

I want the output to be as follows:

rn	code	dateexport	descriptions	countt	price	total
1	aaa	12/24/2021	...	100	2	200
2	bbb	12/24/2021	...	200	3	600
3	ccc	12/24/2021	...	100	2	200
4	ddd	12/24/2021	...	200	3	600
5	eee	12/24/2021	...	200	3	600

Thanks

Answer 1

Just missing PARTITIN BY clause for the code column within the inner query. Rewrite your current query such as

WITH CTE AS
 (SELECT ROW_NUMBER() OVER (PARTITION BY ft.code ORDER BY ft.datesave) AS rn0,         
         ft.code,
         ft.replacement,
         ft.suggcode,
         f.dateexport,
         p.descriptions,
         ft.countt,
         ft.price,
         ft.countt * ft.price AS total,
         p.country
    FROM Productions p
    JOIN FactoriTems ft
      ON p.code = ft.code
    JOIN Factors f
      ON f.gid = ft.gid
   WHERE (ft.gid = @gid))
SELECT ROW_NUMBER() OVER (ORDER BY datesave,code) AS rn, 
       code, replacement, suggcode, dateexport, descriptions,
       countt, price, total, country   
  FROM CTE 
 WHERE rn0 = 1
 ORDER BY rn

in order to filter out the duplicates for each distinct grouped code