CODEWITHSUNDEEP
sqlsql-server
Francesco Mantovani
Francesco Mantovani

Reputation: 12197

SQL: delete duplicate rows with clause

I have a database with all the cities in the world.

and if I run this query I can see there are doubles:

enter image description here

I already used this query to delete all duplicate rows:

WITH CTE AS
(
SELECT *,ROW_NUMBER() 
OVER (PARTITION BY Country, City, AccentCity, Region, Population, latitude, Longitude 
ORDER BY Country, City, AccentCity, Region, Population, latitude, Longitude) AS RN
FROM ExperimentWorld
)

DELETE FROM CTE WHERE RN<>1

I would now run it again and delete all columns where latitude and Longitude are the same and if two rows are the same like in the picture above I want to keep the one where population has some value and delete the one with population '0'

Upvotes: 0

Views: 1180

Answers (1)

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

;WITH CTE
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY Country
            ,City
            ,AccentCity
            ,Region
            ,Population
            ,latitude
            ,Longitude ORDER BY Population DESC
            ) AS RowNum
    FROM ExperimentWorld
    )
DELETE
FROM CTE
WHERE RowNum > 1

Upvotes: 2

Related Questions