How can i remove successive similar rows and keep only the recent row?

Question

I have this table

I want to remove successive similar rows and keep the recent. so the result I want to have is something like this

Answer 1

Here is how I would do it:

;WITH cte AS (
    SELECT valeur, date_reference, id, rownum = ROW_NUMBER() OVER (ORDER BY date_reference) FROM #temperatures
    UNION ALL
    SELECT NULL, NULL, NULL, (SELECT COUNT(*) FROM #temperatures) + 1
)
SELECT A.* FROM cte AS A INNER JOIN cte AS B ON A.rownum + 1 = B.rownum AND COALESCE(a.valeur, -459) != COALESCE(b.valeur, -459)

I am calling the table #temperatures. Use a CTE to assign a ROW_NUMBER to each record and to include an extra record with the last Row_Number (otherwise the last record will not be included in the following query). Then, SELECT from the CTE where the next ROW_NUMBER does not have the same valeur.

Now, if you want to DELETE from the original table, you can review this query's return to make sure you really want to delete all the records not in this return. Then, assuming historique_id is the primary key, DELETE FROM #temperatures WHERE historique_id NOT IN (SELECT historique_id FROM cte AS A....

Answer 2

You can collect all the rows that you want to held in a temp table, truncate your original table, and insert all the rows from temp table to your original table. This will be more effective than just deleting rows in case you have "a lot of duplicates". Also truncate table have following restrictions

You cannot use TRUNCATE TABLE on tables that:

1. Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
2. Participate in an indexed view.
3. Are published by using transactional replication or merge replication.
4. TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL)

In Azure SQL Data Warehouse and Parallel Data Warehouse:

1. TRUNCATE TABLE is not allowed within the EXPLAIN statement.
2. TRUNCATE TABLE cannot be ran inside of a transaction.

You can find more information in following topics.

Truncate in SQL SERVER

Deleting Data in SQL Server with TRUNCATE vs DELETE commands

You can use this script for removing duplicate rows by truncate-insert strategy

 CREATE TABLE #temp_hisorique(
code varchar(50),
code_trim varchar(50),
libelle varchar(50),
unite varchar(50),
valeur varchar(50),
date_reference datetime,
hisoriqueID int
)
GO
;WITH cte AS (
    select *, row_number() over(partition by code, code_trim, libelle, unite, valeur order by date_reference desc) as rownum
    from mytable
)

insert into #temp_hisorique(code, code_trim, libelle, unite, valeur, date_reference, hisoriqueID)
select code, code_trim, libelle, unite, valeur, date_reference, hisoriqueID
from cte
where rownum = 1

TRUNCATE TABLE mytable

insert into mytable(code, code_trim, libelle, unite, valeur, date_reference, hisoriqueID)
select code, code_trim, libelle, unite, valeur, date_reference, hisoriqueID
from #temp_hisorique

Or you can just remove the rows by delete command with join.

;WITH cte AS (
    select *, row_number() over(partition by code, code_trim, libelle, unite, valeur order by date_reference desc) as rownum
    from mytable
)

delete  T
from mytable T
join cte on T.hisoriqueID = cte.hisoriqueID
where cte.rownum > 1