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 :

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:

Thanks

CodePudding user response：

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

CodePudding user response：

Try this code:

WITH CTE AS
(SELECT ROW_Number() over (PARTITION BY FactoriTems.code 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 
WHERE CTE.rn  = 1
ORDER BY rn