Forum Discussion
Unexpected Duplicates in SQL Query
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
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
4 Replies
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
Hello Anonymous
Thank you for your prompt response. I had already implemented it.Best regards,
Ornella
Isn't your query equivalent to
DELETE FROM XXXX_CTRL_CARD
WHERE CAST(DATE_EXEC as DATE) <= GETDATE()
?
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.
