Unexpected Duplicates in SQL Query

[Ornella]

Hello everyone,

Could someone please explain why I'm getting duplicate values even though my query is supposed to prevent them? And if possible, suggest a solution to fix it.

Best regards,

Ornella

[v-bmanikante · Accepted solution]

Hi @Ornella ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@burakkaragoz @lutz_bendlin Thank you for your quick response.

Could you please try the below SQL query:

WITH CTE AS (

    SELECT *,

           ROW_NUMBER() OVER (PARTITION BY CAST(DATE_EXEC AS DATE) ORDER BY DATE_EXEC DESC) AS rn

    FROM XXXX_CTRL_CARD

    WHERE CAST(DATE_EXEC AS DATE) = CAST(GETDATE() AS DATE)

)

DELETE FROM CTE WHERE rn > 1;

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,

B Manikanteswara Reddy

[Ornella]

Hello @v-bmanikante 

Thank you for your prompt response. I had already implemented it.

Best regards,

Ornella

[burakkaragoz]

Hi @Ornella ,

Unexpected duplicates usually show up when your JOIN brings in more rows than expected—especially if the table you're joining to has multiple matching rows.

A couple of things to check:

• Are you joining on a unique key? If not, even a small mismatch can cause row multiplication.
• Try using DISTINCT or GROUP BY to see if that helps reduce the duplicates—but that’s more of a patch than a fix.
• You can also use ROW_NUMBER() or RANK() to filter down to just one row per group if needed.

If you can share a simplified version of your query and table structure, happy to take a closer look!

---

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[lutz_bendlin]

Isn't your query equivalent to 

DELETE FROM XXXX_CTRL_CARD 

WHERE CAST(DATE_EXEC as DATE) <= GETDATE() 

?