Forum Discussion
Remove duplicate values in Fabric Data Warehouse SQL table using Stored Procedure
This SP would work if I want to keep all unique rows but in my case I just want to remove duplicates on my key column [id] but with the highest value in [LastSyncedDate]
Your method will still return duplicate id's
I "solved" this by creating a view in the data warehouse which removes the duplicates and then I just removed the last step of the pipeline and I query the view instead of the table...
Hi Yggdrasill ,
Thanks for using Fabric Community.
Can you please explain me how are you removing the duplicate rows? Are you using the DROP command?
If you are using the DROP command, currently this is not supported in Fabric Warehouse.
Hope this helps. Please let us know if you have any further questions.
I created a stored procedure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE RemoveDuplicates
AS
BEGIN
WITH CTE AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY LastDate DESC) AS RowNum
FROM
dbo.MyTable
)
DELETE FROM CTE
WHERE RowNum > 1;
END;
GO
Hi Yggdrasill ,
I tried to create a repro with a work around by using the CTAS and the DISTINCT keyword in the stored procedure. I have attached the screenshots for your reference.1) Created a stored procedure removeDuplicates .
2) The data in Allotment table is as follows:
3) Executed the stored procedure.
Try using this work around in your stored procedure.
Hope this helps. Please let me know if you have any further questions.