SQL DISTINCT on Multiple Columns

  • Andy DBA (2/8/2011)


    I did not find this to be a good introductory article on DISTINCT. I gave this article 1 star and I have "the nads to say why".

    That's more like it. And with that, I have to apologize to everyone... for one reason or another, I stopped reading just before the aggregate section of the article. It's not a good excuse, just the reason. Thanks for you and some of the others having "the nads", Andy. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • @Sarvesh,

    My recommendation at this point would be to rework the article so it doesn't have the data error with the postal code in the first line of test data and correct the table name error in the SELECT of that same block of code. I'd also recommend that you re-research the aggregate section of the article and write your own example instead of copying from a post... especially since folks have been correct in that area. The two pieces of code are really nothing alike. I know that Steve Jones has no problem posting corrections.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I also consider that these two SQL scripts are completely different and thus shouldnt be considered alternatives for each other

    SELECT PostCode, COUNT(Postcode)

    FROM

    (

    SELECT DISTINCT Address1, Address2, City, Postcode

    FROM AddressTable

    ) AS Sub

    GROUP BY Postcode

    HAVING COUNT(Postcode) > 1

    and

    SELECT Address1,Address2,City,PostCode,Count(PostCode)

    FROM AddressTable

    GROUP BY Address1,Address2,City,PostCode

    HAVING Count(PostCode) > 1

    This. Can it be corrected in the original article?

  • Useful Article, in sense the author explain a new alternate of GROUP BY for DISTINCT ( Surely Unknown to many ).

    For finding the Duplicates, the Query is Okay, but in many situation we are suppose to work on the duplicated data. So instead of Just check the Duplicates, we shall retrieve the total data by the below Query

    -- To Check Duplicate Record

    SELECT Address1,Address2,City,PostCode,Count(PostCode)

    FROM AddressTable

    GROUP BY Address1,Address2,City,PostCode

    HAVING Count(PostCode) > 1

    -- To Retrieve all records identified as Duplicate by some values

    SELECT Address1, Address2, City, Postcode

    FROM AddressTable a

    WHERE (SELECT COUNT(*) FROM AddressTable b

    WHERE a.Address1 = b.Address1 and a.Address2 = b.Address2

    and a.City = b.City and a.Postcode = b.Postcode

    ) >1

    May be useful to some one.....:-D

    Thanks & Regards, Kartik M Kumar..

  • Not to pick on the article too much further since it appears much has been said already but to me it seems the first sentence is off and that always starts me wondering about the whole article and content.

    I would think this:

    The DISTINCT clause works in combination with SELECT and gives you unique date from a database table or tables. The syntax for DISTINCT is show below

    Was meant to be this:

    The DISTINCT clause works in combination with SELECT and gives you unique data from a database table or tables. The syntax for DISTINCT is shown below

    Granted I understand that in many cases the author's first language may not be English but when the very first sentence of the article is flawed in some obvious way I really do wonder about the rest of it. I always wonder how well it was proofed or edited if the very first sentence was seemingly missed.

    Edit: :Whistling: Well, to be precise, it is the first 2 sentences.

    😀 :laugh:

  • I frequently need to examine data in tables when I am developing new stored procedures. Often times because the user departments wanting the new programs don't really fully understand their own requirements, or the complex business data that is held in our tables.

    One of the things I frequently do is to use DISTINCT, and to use GROUP BY, and to use COUNT(*). This is all part of the preliminary requirements gathering and me confirming with users about the make up of their data. And showing them views of their data they've never seen before.

    So, for example, we may have a column containing a particular range of values. But no one knows what the values are or how frequently the same value is repeated for the rows. Typically a row will represent one transaction, or one deal, or one contract, or one part number/manufacturing material, or one customer.. etc.

    So I will typically use the following to see how many distinct values have been used by the business:

    select

    distinct

    unit_of_measure

    from mfg.materials

    So this will show all the different units that have been used by the factory.

    Then I want to see specifically how many of each unit. So I'll use:

    select

    --distinct

    unit_of_measure, count(*) as mycount

    from mfg.materials

    group by unit_of_measure

    order by mycount desc

    This is just an example out of many cases.

    (In case you're wondering why I bother with the distinct and don't just go straight to the group by.... but that's a whole other story.)

    HTH.

  • Andy DBA (2/8/2011)


    ....Please sell me on any reason beside "clarity of code" or "easy solution" (read band-aid fix) where using SELECT DISTINCT would be preferable to using GROUP BY.

    Any time I need to populate a dropdown list with values from a more widely used table, I'll be likely to use SELECT DISTINCT instead of GROUP BY. It gives me the same answer, uses less code and won't need to be expanded to include aggregate functions. That's just one example off the top of my head.

    Semper in excretia, suus solum profundum variat

  • Thato (2/8/2011)


    Good article. Straight to the point. Good examples too. Needs to work a little bit more on presentation,spell check and sort order options for output readability and quick comparison. Another small bit of advice,

    make the habit of using common table expressions. Keep it up. Thank you.

    please explain this ..

    Neeraj Prasad Sharma
    Sql Server Tutorials

Viewing 8 posts - 31 through 38 (of 38 total)

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