Forgot to mention, FMP 13 on OSX 10.9
Getting a future copy of FileMaker is more like it. There is no such "console" for SQL queries built into FileMaker. SQL can be used only in very limited circumstances.
But here's a way to get just one unique instance of each record:
Define a new table with just the fields you want. Use Field options to specify "Unique values, Validate always" on your cities field.
Now use import records to import all records from your current table into the new table. The above field options will filter out the duplicates during the import. (And you can import records from one table to another table in the same file, just follow the same steps you would follow if the tables were in two different files.)
Thanks, PhilModjunk. I couldn't get your import to behave, so I just dumped the table I wanted to rework out to MySQL and made quick work of it where I could use full SQL. Then exported the whole normalized table set and brought it back in. I probably should have been able to do it with external calls, but that got it done.
On the one hand it's good that you found a way to get the job done. On the other hand, what you report indicates that you have more to learn about how to work with FileMaker. Learning how to get the job done within FileMaker using it's tools (and those available through using FileMaker Advanced) will speed your development process. So I encourage you to pursue a better understanding of FileMaker.
No doubt. But then, on this project, Filemaker is just one part of a software ecosystem. If it takes longer to learn than the time it will save over the life of the project, then the tool that saves the most time gets the job. I'm using Filemaker because of the the non-coding interface and the ease of manipulating graphical data. If it gets in the way, I'm happy to move tasks around.