2

I believe GROUP BY in SQL would make DISTINCT unnecessary because if you group by a column then there will only be one of each value in the result, but I want to make sure I am understanding the keywords correctly. Is it the case that you would not need to do this:

SELECT DISTINCT a_uuid
FROM table
GROUP BY a_uuid
HAVING NOT bool_or(type = 'Purchase')

because you could just drop the DISTINCT completely?

tscizzle
  • 11,191
  • 15
  • 54
  • 88

3 Answers3

3

As long as aggregate functions aren't involved you can use DISTINCT instead of GROUP BY.

Use either DISTINCT or GROUP BY - not both!

jarlh
  • 42,561
  • 8
  • 45
  • 63
3

You do not need the distinct in this query. In general, you don't need distinct with group by. There are actually some queries where distinct and group by go together, but they are very rare.

You need group by in this query, because you are using an aggregation function in the having clause. So, use:

SELECT a_uuid
FROM table
GROUP BY a_uuid
HAVING NOT bool_or(type = 'Purchase')
Brad Larson
  • 170,088
  • 45
  • 397
  • 571
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Use DISTINCT if you just want to remove duplicates. Use GROUPY BY if you want to apply aggregate operators (MAX, SUM, GROUP_CONCAT, ..., or a HAVING clause).

If you use DISTINCT with multiple columns, the result set won't be grouped as it will with GROUP BY, and you can't use aggregate functions with DISTINCT.

Overall, these two are different in functionality matter, however, it happens that these two return same output for the particular set of data.

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
  • Ah yes, I realize my question sounds like I'm saying GROUP BY could replace DISTINCT in the *language* but I just meant in certain queries. Thanks for the answer! – tscizzle Dec 17 '14 at 16:07
  • @tscizzle:- Yes, in this query there is no need of Distinct. – HaveNoDisplayName Dec 17 '14 at 16:09
  • 1
    As a matter of fact GROUP BY can replace DISTINCT! "select distinct a, b, c from t" is equal to "select a, b, c from t group by a, b, c"! – jarlh Dec 17 '14 at 16:09