Differences between DISTINCT and GROUP BY?

What is the difference between the two statements, besides the syntax? And
in general what are the differences between the DISTINCT and the GROUP BY
methods? -- Thanks
select field
from table
group by field
select distinct field
from tableDISTINCT is for distinctness.
GROUP BY is for aggregation. It just so happens that part of the
aggregation process is distinctness of all non-aggregated columns, which is
why you see the same behavior when you use GROUP BY with no aggregation,
compared with DISTINCT.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam> wrote
in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
> What is the difference between the two statements, besides the syntax? And
> in general what are the differences between the DISTINCT and the GROUP BY
> methods? -- Thanks
> select field
> from table
> group by field
> select distinct field
> from table
>|||GROUP BY without any aggregate columns is effectively the same as DISTINCT
on the same column set.
Specifically these are the same as far as SQL Server 2005 is concerned.
They return the same results. They will be optimized with the same level of
support in pretty much every case.
SELECT DISTINCT A, B, C FROM Table;
and
SELECT A, B, C FROM Table GROUP BY A, B, C
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eK61kj49FHA.3308@.TK2MSFTNGP11.phx.gbl...
> DISTINCT is for distinctness.
> GROUP BY is for aggregation. It just so happens that part of the
> aggregation process is distinctness of all non-aggregated columns, which
> is why you see the same behavior when you use GROUP BY with no
> aggregation, compared with DISTINCT.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
>|||Thanks. My habit is to throw in "DISTINCT" when I see it is going to
duplicate the results (or that it is already doing it), it's fast and
efficient.
I use GROUP BY when I need to aggregate as you say.
Although Crystal is doing all of my grouping and counting now.

> DISTINCT is for distinctness.
> GROUP BY is for aggregation. It just so happens that part of the
> aggregation process is distinctness of all non-aggregated columns, which
> is why you see the same behavior when you use GROUP BY with no
> aggregation, compared with DISTINCT.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Nicolas Verhaeghe - White Echo" <nospam_nicolas@.whiteecho.com_nospam>
> wrote in message news:4390be2d$0$3756$39cecf19@.news.twtelecom.net...
>|||Hmm, when I see a query that returns duplicates, my first reaction is to
inspect the query, because if this happens the query is usually
incorrect. My first reaction is not to throw in a DISTINCT...
Gert-Jan
Nicolas Verhaeghe wrote:
> Thanks. My habit is to throw in "DISTINCT" when I see it is going to
> duplicate the results (or that it is already doing it), it's fast and
> efficient.
> I use GROUP BY when I need to aggregate as you say.
> Although Crystal is doing all of my grouping and counting now.
>