I have a Table that updates everyday with new data for the day, but the overall Table contains duplicate rows. I'm looking for a custom SQL query by which I can create another table that takes in table one and outputs to a new table with only unique rows, thus removing all duplicate rows. This way I can stay with a one-to-one relationship when I merge Table 2 with a Fact table.
There is one column that identifies if the value is unique, AccountID.
I have tried:
SELECT * FROM
(SELECT DISTINCT r.AccountID
FROM [DIM_CLEARING_ACC_STATIC] r) AS DIM
But that only returns the AccountID column, not the whole row. I have also tried some SQL examples, but Sisense does not have all the functions needed, I tried:
WITH CTE AS(
SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7],
RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1)
DELETE FROM CTE WHERE RN > 1
But Siense does not have some of the functions involved in that query.
Can you please indicate how could I get perform such query?
Please sign in to leave a comment.