Group By v/s Distinct

Question

Group By v/s Distinct

Mahesh Bote

SSCrazy Eights

Points: 9107
More actions
March 13, 2008 at 3:05 am

#183223

[font="Verdana"]Hi All,
Can anybody tell me, in detail, what is the difference between Group By and Distinct?
Thanks in advance,
Mahesh[/font]
MH-09-AM-8694

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

First tell me which part of Books Online about DISTINCT and GROUP BY you didn't understand, and I can maybe tell you some more.

N 56°04'39.16"
E 12°55'05.25"

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 2

Can anybody tell me, in detail, what is the difference between a genuine request for help and homework?

😉

GSquared SSC Guru Points: 260824 More actions · Answer 3

Well, for one thing, they don't have any letters in common with each other. Secondly, one of them is two words, while the other is only one word. Third, they have a different number of letters.....

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sarvesh Kumar Gupta SSC Eights! Points: 821 More actions · Answer 4

Hi,

Group By clause is used for grouping the data with one of the field whereas distinct is used for removing duplicate values.

Sarvesh Kumar Gupta 😎

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks a lot Sarversh for such a Humble reply. (En firangiyonki to mai ek din **** ...) 😉

GSquared and Leo Mepham, ... I will develop one forum site. Obviously it will be for you both only. And keep posting such SMART replies over there. 😛 ...

Thanks again,

Mahesh

(INDIA)

MH-09-AM-8694

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

Well, Sarvesh is not entirely correct.

You can use GROUP BY to sort out duplicate values. In most cases GROUP BY is more efficient than DISTINCT to do that because GROUP BY works on a deeper level (earlier in the process) than DISTINCT (which is applied last).

DISTINCT can be used to aggregate records (together with a correlated subquery), but this is another topic.

N 56°04'39.16"
E 12°55'05.25"

Sarvesh Kumar Gupta SSC Eights! Points: 821 More actions · Answer 7

Hi peso,

Ok if u r right then give me a query by using group by clause for removing duplicate values from a table. and without using a aggregate function.

Regards

Sarvesh Kumar Gupta 😎

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

Other than this?DELETEf

FROM(

SELECTROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID

FROMTable1

) AS f

WHEREf.RecID > 1

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Sarvesh Kumar Gupta (3/14/2008)
Ok if u r right then give me a query by using group by clause for removing duplicate values from a table. and without using a aggregate function.

If you show me how to do it with DISTINCT without using this technique

SELECT DISTINCT *

INTO #Temp

FROM Table1

DELETE

FROM Table1

INSERT Table1

SELECT * FROM #Temp

DROP TABLE #Temp

N 56°04'39.16"
E 12°55'05.25"

Leo Mepham Ten Centuries Points: 1045 More actions · Answer 10

Mahesh (3/14/2008)
Thanks a lot Sarversh for such a Humble reply. (En firangiyonki to mai ek din **** ...) 😉
GSquared and Leo Mepham, ... I will develop one forum site. Obviously it will be for you both only. And keep posting such SMART replies over there. 😛 ...
Thanks again,
Mahesh
(INDIA)

Ok, for being a little smarmy here's my punishment, hope it helps 😉

DECLARE @TABLE TABLE

(

ID INT NOT NULL

)

INSERT INTO @TABLE

SELECT 1 UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5

--Distinct List

SELECT ID FROM @TABLE

GROUP BY ID

--Non-Distinct List

SELECT ID FROM @TABLE

shamas saeed SSC-Addicted Points: 458 More actions · Answer 11

Group BY filter distinct records on base of Group Columns.

Distinct filter records on base of all columns in select.

For more information go to sql server central help.