Dillon_Su Dillon_Su - 2 months ago 21
SQL Question

SQL Eliminate Duplicates with NO ID

I have a table with the following Columns...
Node, Date_Time, Market, Price

I would like to delete all but 1 record for each Node, Date time.

SELECT Node, Date_Time, MAX(Price)
FROM Hourly_Data
Group BY Node, Date_Time


That gets the results I would like to see but cant figure out how to remove the other records.

Note - There is no ID for this table

Answer

here is an easy sql-server method that creates a Row Number within a cte and deletes from it. I believe this method also works for most RDBMS that support window functions and Common Table Expressions.

;WITH cte AS (
    SELECT
       *
       ,RowNum = ROW_NUMBER() OVER (PARTITION BY Node, Date_Time ORDER BY Price DESC)
    FROM
       Hourly_Data
)

DELETE
FROM
    cte
WHERE
    RowNum > 1