Eliminate duplicate rows using MySQL DISTINCT

I have a world db. The table below was generated with:

SELECT *
FROM geolocations
WHERE city = 'Santa Cruz'

The table contains multiple entries for each city, it once contained a Zip Code field. I want to delete duplicate entries for cities. I tried using:

CREATE TABLE tmp
SELECT DISTINCT city, region
FROM geolocations

The previous statement eliminates the duplicate entries, but how do I get id and other fields to copy to new table?

-----------------------------------------------------------------------
id    Country   Region     City       Latitutde         Longitude
-----------------------------------------------------------------------
4683    US  CA  Santa Cruz  37.0447998047   -122.1020965576      
5748    US  CA  Santa Cruz  36.971开发者_开发问答2982178   -121.9875030518      
9506    US  CA  Santa Cruz  37.0101013184   -122.0324020386      
11205   US  CA  Santa Cruz  37.0344009399   -121.9796981812      
11379   US  CA  Santa Cruz  36.9898986816   -122.0603027344      
13146   US  CA  Santa Cruz  37.0101013184   -122.0324020386      
14362   US  CA  Santa Cruz  37.0101013184   -122.0324020386      
30055   BO  03  Santa Cruz  -12.2833003998  -66.2500000000   
31760   ES  59  Santa Cruz  42.6666984558   -2.3499999046    
39477   AR  22  Santa Cruz  -27.6667003632  -64.2667007446   
-----------------------------------------------------------------------

---

As Marvo said those are not duplicate rows perse but some fields duplicated. The best approach could be to redesign your database and break that table into two -at least-. This could serve you as a starting point:

First create a table that contains different-uniques- cities:

CREATE TABLE city SELECT DISTINCT city FROM db

Add a primary key to that table:

ALTER TABLE city ADD id_city INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

Create a table that contains all your records and the id of the corresponding city that each record belongs to:

CREATE TABLE records SELECT db.id,db.country, db.region, city.id_city, db.lat,db.long FROM db INNER JOIN city ON db.city = city.city

You can make another one for cities asocciated with countries if you wish.

---

This query worked I wanted to select distinct city, region combinations and group it with its associated data in the new table.

CREATE TABLE clean_gls SELECT * FROM geolocations GROUP BY city, region;

---

IN MSSQL it's this"

SELECT distinct City, Region
INTO tmp
FROM geolocations 
WHERE City = 'Santa Cruz'

In MYSQL, you can use select into table

Although I havent tested it, something like this should work:

INSERT INTO tmp(City, Region)
    SELECT distinct City, Region
    FROM geolocations WHERE WHERE City = 'Santa Cruz'