I DISTINCTly hate MySQL (help building a query)

Question

This is staight forward I believe:

I have a table with 30,000 rows. When I SELECT DISTINCT 'location' FROM myTable it returns 21,000 rows, about what I'd expect, but it only returns that one column.

What I want is to move those to a new table, but the whole row for each match.

My best guess is something like SELECT * from (SELECT DISTINCT 'location' FROM myTable) or something like that, but it says I have a vague syntax error.

Is there a good way to grab the rest of each DISTINCT row and move it to a new table all in one go?

Answer 1

SELECT * FROM myTable GROUP BY `location`

or if you want to move to another table

CREATE TABLE foo AS SELECT * FROM myTable GROUP BY `location`

Answer 2

Distinct means for the entire row returned. So you can simply use

SELECT DISTINCT * FROM myTable GROUP BY 'location'

Using Distinct on a single column doesn't make a lot of sense. Let's say I have the following simple set

-id-   -location-
1       store
2       store
3       home

if there were some sort of query that returned all columns, but just distinct on location, which row would be returned? 1 or 2? Should it just pick one at random? Because of this, DISTINCT works for all columns in the result set returned.