Is there any purpose for using both DISTINCT and GROUP BY in SQL?
Below is a sample code
SELECT DISTINCT Actors FROM MovieDetails GROUP BY Actors
Does anyone know of any situations where both DISTINCT and GROUP BY need to be used, to get any specific desired results?
(The general usage of DISTINCT and GROUP BY separately is understood)
This question is tagged with
~ Asked on 2015-09-21 18:35:53
DISTINCTto remove duplicate
GROUPING SETSfrom the
In a completely silly example using
GROUPING SETS() in general (or the special grouping sets
CUBE() in particular), you could use
DISTINCT in order to remove the duplicate values produced by the grouping sets again:
SELECT DISTINCT actors FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors) GROUP BY CUBE(actors, actors)
actors ------ NULL a b
actors ------ a b NULL a b a b
But why, apart from making an academic point, would you do that?
DISTINCTto find unique aggregate function values
In a less far-fetched example, you might be interested in the
DISTINCT aggregated values, such as, how many different duplicate numbers of actors are there?
SELECT DISTINCT COUNT(*) FROM (VALUES('a'), ('a'), ('b'), ('b')) t(actors) GROUP BY actors
count ----- 2
DISTINCTto remove duplicates with more than one
Another case, of course, is this one:
SELECT DISTINCT actors, COUNT(*) FROM (VALUES('a', 1), ('a', 1), ('b', 1), ('b', 2)) t(actors, id) GROUP BY actors, id
actors count ------------- a 2 b 1
actors count ------------- a 2 b 1 b 1
For more details, I've written some blog posts, e.g. about
GROUPING SETS and how they influence the
GROUP BY operation, or about the logical order of SQL operations (as opposed to the lexical order of operations).
~ Answered on 2015-09-21 19:09:42
Perhaps not in the context that you have it, but you could use
SELECT DISTINCT col1, PERCENTILE_CONT(col2) WITHIN GROUP (ORDER BY col2) OVER (PARTITION BY col1), PERCENTILE_CONT(col2) WITHIN GROUP (ORDER BY col2) OVER (PARTITION BY col1, col3), FROM TableA
You would use this to return different levels of aggregation returned in a single row. The use case would be for when a single grouping would not suffice all of the aggregates needed.
~ Answered on 2015-09-21 19:06:28