SQL: delete duplicate rows with clause

Question

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

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

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'

Add a where clause. Delete from cte where ... population < 1 and.... — S3S
– S3S, Commented Jun 3, 2017 at 5:04

Maverick Sachin · Accepted Answer · 2017-06-03 05:09:08Z

2

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

answered Jun 3, 2017 at 5:09

Maverick Sachin

8837 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Francesco Mantovani Over a year ago

Yes it worked, I had edit and select just 'latitude,Longitude'

Collectives™ on Stack Overflow

SQL: delete duplicate rows with clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related