Dillon_Su Dillon_Su - 1 year ago 105
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 Source

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 (
       ,RowNum = ROW_NUMBER() OVER (PARTITION BY Node, Date_Time ORDER BY Price DESC)

    RowNum > 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download