Find Cities in Each State - Problem

You are given a table cities that contains state and city information. Each row contains a state name and a city name within that state.

Your task: Write a SQL query to find all cities in each state and combine them into a single comma-separated string.

Requirements:

  • Group cities by state
  • Concatenate all city names with commas
  • Order cities alphabetically within each state
  • Order states alphabetically in the final result

Table Schema

cities
Column Name Type Description
state PK varchar State name
city PK varchar City name within the state
Primary Key: (state, city)
Note: Each row represents a unique state-city combination

Input & Output

Example 1 — Multiple States with Multiple Cities
Input Table:
state city
California Los Angeles
California San Francisco
California San Diego
Texas Houston
Texas Austin
Texas Dallas
New York New York City
New York Buffalo
New York Rochester
Output:
state cities
California Los Angeles, San Diego, San Francisco
New York Buffalo, New York City, Rochester
Texas Austin, Dallas, Houston
💡 Note:

The query groups cities by state and concatenates them alphabetically. California gets 3 cities, Texas gets 3 cities, and New York gets 3 cities, all ordered alphabetically within each state.
Example 2 — Single City per State
Input Table:
state city
Alaska Anchorage
Hawaii Honolulu
Nevada Las Vegas
Output:
state cities
Alaska Anchorage
Hawaii Honolulu
Nevada Las Vegas
💡 Note:

When each state has only one city, the STRING_AGG function simply returns that single city name without any commas.
Example 3 — Two Cities per State
Input Table:
state city
Florida Tampa
Florida Miami
Oregon Salem
Oregon Portland
Output:
state cities
Florida Miami, Tampa
Oregon Portland, Salem
💡 Note:

Each state has exactly two cities. The cities are ordered alphabetically within each state: Miami before Tampa for Florida, and Portland before Salem for Oregon.

Constraints

  • 1 ≤ number of rows ≤ 1000
  • state and city contain only valid string characters
  • (state, city) combination is unique (primary key constraint)

Visualization

Tap to expand
Find Cities in Each State INPUT TABLE state city California Los Angeles California San Diego California San Francisco Texas Austin Texas Dallas Texas Houston New York Albany New York Buffalo cities table with state and city columns ALGORITHM STEPS 1 ORDER BY city Sort cities alphabetically within each state 2 GROUP BY state Group rows by state for aggregation 3 STRING_AGG/GROUP_CONCAT Concatenate cities with comma separator 4 ORDER BY state Sort final results by state name SELECT state, STRING_AGG(city, ', ') FROM cities GROUP BY state ORDER BY state FINAL RESULT state cities California Los Angeles, San Diego, San Francisco New York Albany, Buffalo Texas Austin, Dallas, Houston OK - Complete States sorted A-Z Cities within each state also sorted A-Z 3 rows returned Key Insight: STRING_AGG (PostgreSQL/SQL Server) or GROUP_CONCAT (MySQL) aggregates multiple row values into a single comma-separated string. The ORDER BY inside the aggregate function controls the order of concatenated values, while the final ORDER BY sorts the grouped results. Time Complexity: O(n log n) for sorting operations. TutorialsPoint - Find Cities in Each State | Optimal Solution

Related Problems

Asked in
Amazon 28 Google 22 Microsoft 18 Facebook 15
23.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen