Quick SQL help please

I have table with these fields:
name, city

Sample data:
John Doe, Chicago
Jane Doe, New York
Jim Smith, Dallas
Mike Lee, New York
John Ash , Dallas
Pete Smith, Chicago
Bob Goon, Chicago

I’d like to create a report listing exactly like this:

Bob Goon
John Doe
Pete Smith

New York:
Jane Doe
Mike Lee

Jim Smith
John Ash

Thanks in advance for any/all help.

I used PHP commands, but the comments should be clear enough to implement it on other languages as well, I hope :slight_smile:

// execute the query to get your data, ordered by city, name

// set the current city variable
$current_city = '';

// loop through the query result set
while ($row = mysql_fetch_assoc($result)) {

  // check if a new city has come up
  if ($current_city != $row['city']) {
    // if so, display the city info
    // and update the current cityvariable
    $current_city = $row['city'];
  // display the other info

Select the data from the table, ordered by city, name
Then loop through it with your server side language (PHP?) and display the city name each first time, and all names below it.

Could you possibly post a (pseudo) code for this as an example?


Thanks Guido.