I need to eliminate the duplicates and in the case of ID_CLIENTE7075593 that the date is different I must leave the oldest date in this case record #9 would have to be deleted since the date is greater than that of record #10
For this, there is nothing better than grouping the results (that leaves only one) and taking the smallest date of all, which, as it is a number, has even fewer problems.
Select territorial, id_cliente, min(fecha)
from CLIENTES
group by territorial, id_cliente
With this, you are saying to group by the same territory, and then group by id_client (which is what leaves you with a single row per client) and then, you are only keeping the minimum date, which as they are in ansi, but at reverse (when one saves dates in that format, like a number, which is called ansi, they are saved in yyyymmdd format, because otherwise it is difficult to order them later), with a min, we obtain the smallest...
Detail of how to remove duplicate records from a table in SQL Server:
1.- The first step is to obtain the oldest Id per duplicate client with a Min on the Client Id and grouping by the fields of the table to obtain the duplicates.
2.- The second step is to actually delete the duplicate records.
The specific script is the following:
BEGIN TRANSACTION
DELETE FROM [CLIENTES]
WHERE ID_CLIENTE NOT IN
(
SELECT MIN(ID_CLIENTE )
FROM [CLIENTES]
GROUP BY CamposDeLaTabla
)
ROLLBACK TRANSACTION
3.- If everything is ok, comment the rollback code and put COMMIT TRANSACTION
For this, there is nothing better than grouping the results (that leaves only one) and taking the smallest date of all, which, as it is a number, has even fewer problems.
With this, you are saying to group by the same territory, and then group by id_client (which is what leaves you with a single row per client) and then, you are only keeping the minimum date, which as they are in ansi, but at reverse (when one saves dates in that format, like a number, which is called ansi, they are saved in yyyymmdd format, because otherwise it is difficult to order them later), with a min, we obtain the smallest...
Detail of how to remove duplicate records from a table in SQL Server:
1.- The first step is to obtain the oldest Id per duplicate client with a Min on the Client Id and grouping by the fields of the table to obtain the duplicates.
2.- The second step is to actually delete the duplicate records.
The specific script is the following:
3.- If everything is ok, comment the rollback code and put COMMIT TRANSACTION
Greetings, I hope and be useful.