CODEWITHSUNDEEP
sqlsql-serverduplicates
Dillon_Su
Dillon_Su

Reputation: 91

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

Upvotes: 0

Views: 41

Answers (2)

Matt
Matt

Reputation: 14341

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

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

Here are steps that are rather workaround than a simple one-command which will work in any relational database:

  1. Create new table that looks just like the one you already have
  2. Insert the data computed by your group-by query to newly created table
  3. Drop the old table
  4. Rename new table to the name the old one used to have

Just remember that locking takes place and you need to have some maintenance time to perform this action.

There are simpler ways to achieve this, but they are DBMS specific.

Upvotes: 1

Related Questions