|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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 table 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. -- Show quoteAdam 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 Show quote "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... >> 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 >> > > 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. Show quote > 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... >> 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 >> > > 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: Show quote > > 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... > >> 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 > >> > > > >  |
|||||||||||||||||||||||
Other interesting topics