SELECT with GROUP BY can be used as an alternative to SELECT DISTINCT. Pranaya Rana example is essentially equivalent
SELECT DISTINCT UserName, Departmentid FROM user
If you need an example where the GROUP BY over DISTINCT preference is justified, here is one. Let's say you want to return elements that occur only once in the table:
SELECT Item FROM atable GROUP BY Item HAVING COUNT(*) = 1
As you can see, the query returns only a non-aggregated column.
It still uses an aggregate function, though, even if not in the SELECT clause. There may be situations where you should prefer GROUP BY DISTINCT, although you do not need to use aggregate functions at all, but at this stage I should be defined on the platform.
In particular, if you use SQL Server, you will find the difference between the results of the following two statements:
# 1:
WITH data (f) AS ( SELECT 'abc' UNION ALL SELECT 'def' UNION ALL SELECT 'abc' ) SELECT f, ROW_NUMBER() OVER (ORDER BY f) FROM data GROUP BY f
# 2:
WITH data (f) AS ( SELECT 'abc' UNION ALL SELECT 'def' UNION ALL SELECT 'abc' ) SELECT DISTINCT f, ROW_NUMBER() OVER (ORDER BY f) FROM data
(If you do not, you can still see the results for yourself using the Stack Exchange Data Explorer .)
Both operators are designed to return individual items. However, only the first statement (with GROUP BY) returns the results as expected, while the last (with DISTINCT) returns all the elements. Obviously, the ranking functions in SQL Server are evaluated after GROUP BY, but before DISTINCT. The reason is that the SELECT clause in SQL Server is evaluated after GROUP BY, but before DISTINCT, as a result of which you prefer GROUP BY over DISTINCT in this case. (Thanks @ypercube for pushing me in the right direction.)