Using SELECT DISTINCT in MYSQL

Question

Been doing a lot of searching and haven't really found an answer to my MYSQL issue.

SELECT DISTINCT name, type, state, country FROM table

Results in 1,795 records

SELECT DISTINCT name FROM table

Results in 1,504 records

For each duplicate "name"... "type", "state", "country" aren't matching in each record.

Trying to figure out how to SELECT the associated row to the DISTINCT name, without checking them for being DISTINCT or not

I'm afraid I'm having some difficulty understanding your questions. Could you post a cut-down version of the data, and explain what it is you want? — Chowlett, Jun 22 '10 at 08:15
Something similar: http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns-postgresql — jigfox, Jun 22 '10 at 08:16
you might want to add a sample of the output. something along the lines of ... here are 3 rows with duplicate name but different type, state, country. [3 rows here :-)] and i want [desired output rows] — potatopeelings, Jun 22 '10 at 08:24

score 18 · Accepted Answer · answered Jun 22 '10 at 08:15

18

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

should do the trick.

answered Jun 22 '10 at 08:15

cypher

6,822
4
31
48

what would be the full query to SELECT DISTINCT by group? – ZaneDeFazio Jun 22 '10 at 08:16
I'm not quite sure what you're asking for. Probably my fault, haven't slept today :-) – cypher Jun 22 '10 at 08:18
3

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 Jun 22 '10 at 08:19
3

Did you check that query? IMO there need to be any form of aggregation on any column not in the group by clause. – Janick Bernet Jun 22 '10 at 08:20
I had no idea that GROUP BY only would group unique names. That did do the trick, thank you very much! – ZaneDeFazio Jun 22 '10 at 08:23
Not really, this will select exactly one row for every unique name, other rows will be ignored. – cypher Jun 22 '10 at 08:23
1

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. – Aliaksei Kliuchnikau Jun 22 '10 at 08:31
@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 table while($row = mysql_fetch_assoc($sql)) { } – ZaneDeFazio Jun 22 '10 at 08:45
If i got it right, what you want to do is this? ? – cypher Jun 22 '10 at 08: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 Jun 22 '10 at 08:59
1

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 Jun 22 '10 at 11:26
2

@FrantišekŽiačik what it returns depends on the ordering. E.g.: `GROUP BY name ORDER BY state DESC` would return each unique name with the highest state for that name. Even though MySQL violates the SQL standard, I find this behavior quite useful at times. – Fabrício Matté Feb 12 '14 at 20:30
Warning to others stumbling on this question: Be very careful to pay attention to this discussion regarding aggregates on the non-grouped columns! The code in this answer, though it's technically correct, is dangerous and misleading. – Dan Nissenbaum Oct 26 '14 at 18:09

score 1 · Answer 2 · answered Jun 22 '10 at 08:24

1

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

answered Jun 22 '10 at 08:24

František Žiačik

7,511
1
34
59

Using SELECT DISTINCT in MYSQL

2 Answers2

Linked