ansaurus

Question

Answer 1

A:

SELECT name, type, state, country FROM table GROUP BY name;

should do the trick.

cypher 2010-06-22 08:15:33

what would be the full query to SELECT DISTINCT by group?

ZaneDeFazio 2010-06-22 08:16:58

I'm not quite sure what you're asking for. Probably my fault, haven't slept today :-)

cypher 2010-06-22 08:18:04

you don't need distinct, for a columns that is grouped. as defined `group by name` summarizes everything with the same `name`. so every `name` is unique in the result

jigfox 2010-06-22 08:19:05

Did you check that query? IMO there need to be any form of aggregation on any column not in the group by clause.

inflagranti 2010-06-22 08:20:59

I had no idea that GROUP BY only would group unique names. That did do the trick, thank you very much!

ZaneDeFazio 2010-06-22 08:23:09

Not really, this will select exactly one row for every unique name, other rows will be ignored.

cypher 2010-06-22 08:23:32

Yo're welcome :-)

cypher 2010-06-22 08:23:50

Please note: According to this article http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. When you group by 'name' and there are several 'types'/'states'/'countries' for a single 'name' value, then MySQL will take indeterminate 'type'/'state'/'country' value for each 'name' in the result set.

Alex 2010-06-22 08:31:36

@cypher when trying to create a mysql_query in PHP with that query I get "Resource id#3"$sql = mysql_query('SELECT name, type, state, country FROM table GROUP BY name');Goal would be able to do a while loop to INSERT each row into a new tablewhile($row = mysql_fetch_assoc($sql)) { }

ZaneDeFazio 2010-06-22 08:45:01

If i got it right, what you want to do is this? <?php $q = mysql_query('SELECT blabla GROUP BY something'); while($row = mysql_fetch_assoc($q)) mysql_query('INSERT INTO some_table (some_column, some_other_column) VALUES ("'.$row['some_value'].'", "'.$row['other_value'].'")' ); ?> ?

cypher 2010-06-22 08:50:28

@cypher exactly

ZaneDeFazio 2010-06-22 08:52:50

And does it work now? Edit: by the way, the right way to do this is to create a stored procedure http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html, it will be much faster than this, you'll only need to mysql_query('call MyProcedure()');

cypher 2010-06-22 08:59:31

Very suprising for me that mysql doesn't require specifying aggregates on non-grouped columns. What does it return in them? Mins?

František Žiačik 2010-06-22 11:26:23

Answer 2

A:

If you want distinct name, you must decide which of the multiple values that may occur for each distinct name you want. For example, you may want minimals, or counts:

SELECT name, min(type), min(state), count(country) FROM table GROUP BY name

František Žiačik 2010-06-22 08:24:51

ansaurus

tags:

views:

answers:

Using SELECT DISTINCT in MYSQL

related questions