I REFUSE hate MySQL (help build the query)

Question

This is true 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 expect, but returns only one column.

I want to move them to a new table, but the entire row for each match.

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

Is there a good way to grab the rest of each row of DISTINCT and move it to a new table at a time?

Answer 1

SELECT * FROM myTable GROUP BY `location`

or if you want to switch to another table

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

Answer 2

.

SELECT DISTINCT * FROM myTable GROUP BY 'location'

Distinct . ,

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

- , , , ? 1 2? ? - DISTINCT .

Answer 3

, , .

, , -, , :

 Location     OtherCol   StillOtherCol

 Place1       1          Fred
 Place1       89         Fred
 Place1       1          Joe

, ? DISTINCT, , , DISTINCT . ( , : SELECT DISTINCT * FROM YourTable).

, , (, IMHO) MySQL SQL :

*

, , undefined.

Answer 4

Multiple rows with the same values ​​in all columns make no sense. OK - the question may be a way to fix this particular situation.

Given this table, with the identifier is 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 can extract a sample for each single no (: = location) by grouping by this column and selecting a row with a 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