ansaurus

Question

Don't use DISTINCT when you have or could use GROUP BY

Answer 1

+4 A:

SELECT Code
FROM YourTable
GROUP BY Code

vs

SELECT DISTINCT Code
FROM YourTable

astander 2009-12-11 13:07:29

Answer 2

+5 A:

If you know that two columns from your result are always directly related then it's slower to do this:

SELECT DISTINCT CustomerId, CustomerName FROM (...)

than this:

SELECT CustomerId, CustomerName FROM (...) GROUP BY CustomerId

because in the second case it only has to compare the id, but in the first case it has to compare both fields. This is a MySQL specific trick. It won't work with other databases.

Mark Byers 2009-12-11 13:10:18

Those two queries aren't the same, are they? In the second one you're only selecting distinct IDs, not distinct names as well.

DisgruntledGoat 2009-12-11 14:30:39

This won't work with any other T-SQL, or it won't be faster with any other one?

Norla 2009-12-11 14:57:12

Well the second one doesn't work!!! Any Column not included in the GROUP BY clause requires an aggregation function.

Craig Young 2009-12-11 14:59:57

Craig Young: really? http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html "MySQL extends the use of GROUP BY so that you can use nonaggregated columns or calculations in the SELECT list that do not appear in the GROUP BY clause." What version of MySQL are you using? Maybe you have turned this feature off? "This extension does not apply if the ONLY_FULL_GROUP_BY SQL mode is enabled."

Mark Byers 2009-12-11 19:14:46

@Mark: Lets assume you have the following data for Col1, Col2 ungrouped: {1,First}{1,Second}; If you `select Col1, Col2 from (...) group by Col1` - Which of the 2 possible unaggregated values should be selected from Col2? The query is ambiguous, and as such should **NEVER** be allowed to work.

Craig Young 2009-12-13 08:24:45

Answer 3

A:

The basic rule : Put all the columns from the SELECT clause into the GROUP BY clause

so

SELECT DISTINCT a,b,c FROM D

becomes

SELECT a,b,c FROM D GROUP BY a,b,c

__mme__ 2009-12-11 14:14:57

Correct me if I'm wrong. Shouldn't the basic rule be, put all non-aggregate columns from the SELECT clause in the GROUP BY clause?

Norla 2009-12-14 14:54:24

ansaurus

tags:

views:

answers:

Don't use DISTINCT when you have or could use GROUP BY

related questions