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.

Answer 3

Well, first you need to decide what you really want returned.

The problem is that, presumably, for some of the location values in your table there are different values in the other columns even when the location value is the same:

 Location     OtherCol   StillOtherCol

 Place1       1          Fred
 Place1       89         Fred
 Place1       1          Joe

In that case, which of the three rows do you want to select? When you talk about a DISTINCT Location, you're condensing those three rows of different data into a single row, there's no meaning to moving the original rows from the original table into a new table since those original rows no longer exist in your DISTINCT result set. (If all the other columns are always the same for a given Location, your problem is easier: Just SELECT DISTINCT * FROM YourTable).

If you don't care which values come from the other columns you can use a (bad, IMHO) MySQL extension to SQL and do:

SELECT * FROM YourTable GROUP BY Location

which will give a result set with one row per location and values for the other columns derived from the original data in an undefined fashion.

Answer 4

Multiple rows with identical values in all columns don't have any sense. OK - the question might be a way to correct exactly that situation.

Considering this table, with id being the PK:

kram=# select * from foba;
 id | no |     name      
----+----+---------------
  2 |  1 | a
  3 |  1 | b
  4 |  2 | c
  5 |  2 | a,b,c,d,e,f,g

you may extract a sample for every single no (:=location) by grouping over that column, and selecting the row with minimum PK (for example):

SELECT * FROM foba WHERE id IN (SELECT  min (id) FROM foba GROUP BY no); 
 id | no | name 
----+----+------
  2 |  1 | a
  4 |  2 | c