Group By v/s Distinct

  • [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

  • 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"

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

    😉

  • 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

  • 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 😎

  • 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

  • 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"

  • 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 😎

  • 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"

  • 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"

  • 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 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 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

  • 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.

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

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