S16
S16

Reputation: 2995

Using MySQL syntax to select based on unique columns?

I have a database with a listing for every city in the US based on zip code. By nature it has a city/state listing for every zip code in the US which produces several city/state duplicates. I want to perform a SELECT that returns a single row for each city/state.

I have included a screenshot of the database structure for reference. num is PRIMARY.

What would the syntax be for this?

alt text

Upvotes: 0

Views: 545

Answers (2)

Kyle Wild
Kyle Wild

Reputation: 8915

SELECT DISTINCT can do this.

SELECT DISTINCT city, state FROM table_name;

Upvotes: 1

Mark Elliot
Mark Elliot

Reputation: 77074

One simple solution is simply to group by the city, then by the state (in case there are overlaps on city names across states):

SELECT * FROM ziptable GROUP BY city, state

Upvotes: 1

Related Questions