Distinct clause in Select statement

  • dave hants

    Default port

    Points: 1485

    Comments posted to this topic are about the item Distinct clause in Select statement

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - clear and to the point ..yet it makes one think and learn

    Thank you

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • ziangij

    SSCertifiable

    Points: 7207

    good one... thanks 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21987

    Answer is very easy: SELECT DISTINCT does not have parameters.

  • Duncan Pryde

    SSCertifiable

    Points: 7956

    Nice - one of those questions that makes you pause for a couple of seconds and think "Could there be an obscure use of this keyword that I'm not aware of?" And then you realise you're being silly, give yourself a slap on the wrist and click submit...

    Duncan

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Nice question, but I think the answer is a little unclear.

    The important thing to note is that the parentheses of the example do not have anything to do with the DISTINCT keyword. This should become more obvious with the following example:

    SELECT DISTINCT

    NULL Dummy

    ,(datepart(year,ModifiedDate)) as year

    ,(ModifiedDate)

    ,(1) + (2)

    from Person.Contact

    order by ModifiedDate

    These parentheses are valid, but also superfluous as they provide no additional value.

    Best Regards,

    Chris Büttner

  • Andrew Watson-478275

    SSCarpal Tunnel

    Points: 4652

    I agree with Christian, and think that this is a very important point to make. From doing peer reviews, I've found that it's a very common misconception that DISTINCT takes a parameter which in some way restricts its "distinctness".

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    Thanks. Good question, made me think for a minute or two before answering.

  • webrunner

    SSC-Dedicated

    Points: 30305

    Good question, interesting to learn that the parentheses make no difference for SELECT DISTINCT.

    I have one question, though. I've seen people show me queries where they write SELECT COUNT(DISTINCT userid) or something like that. Is that also the same as SELECT DISTINCT COUNT(userid), or is there a difference?

    And I am also curious about how (1) SELECT COUNT(DISTINCT userid) would differ from (2) SELECT COUNT(userid) - I assume if there are multiple rows with the same userid in the table being queried, the second query would return more rows than the first? **

    ** Edited again - sorry, I guess these queries would return the same number of rows (e.g. one summary row), but maybe different COUNT values. Is that correct?

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Ron McCullough

    SSC Guru

    Points: 63877

    webrunner

    Easy enough to find out ...

    CREATE TABLE #T(userid INT)

    INSERT INTO #T

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7

    SELECT COUNT(DISTINCT userid) AS '(1)' FROM #T

    SELECT DISTINCT COUNT(userid)AS '(2)' FROM #T

    Results:

    (1) (2)

    7 8

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Luke L

    SSC-Dedicated

    Points: 35530

    webrunner (7/26/2010)


    Good question, interesting to learn that the parentheses make no difference for SELECT DISTINCT.

    I have one question, though. I've seen people show me queries where they write SELECT COUNT(DISTINCT userid) or something like that. Is that also the same as SELECT DISTINCT COUNT(userid), or is there a difference?

    And I am also curious about how (1) SELECT COUNT(DISTINCT userid) would differ from (2) SELECT COUNT(userid) - I assume if there are multiple rows with the same userid in the table being queried, the second query would return more rows than the first? **

    ** Edited again - sorry, I guess these queries would return the same number of rows (e.g. one summary row), but maybe different COUNT values. Is that correct?

    Thanks,

    webrunner

    AS per The Count() BOL Entry

    The Distinct Keyword is a argument of the Count() Function and is not the same as when used like SELECT DISTINCT col1, col2 from mytable;

    For that use reference the SELECT Clause BOL Entry that states:

    DISTINCT

    Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

    -Luke.

    Edited to fix quote.

    To help us help you read this[/url]

    For better help with performance problems please read this[/url]

  • webrunner

    SSC-Dedicated

    Points: 30305

    bitbucket-25253 (7/26/2010)


    webrunner

    Easy enough to find out ...

    CREATE TABLE #T(userid INT)

    INSERT INTO #T

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7

    SELECT COUNT(DISTINCT userid) AS '(1)' FROM #T

    SELECT DISTINCT COUNT(userid)AS '(2)' FROM #T

    Results:

    (1) (2)

    7 8

    Thanks - that's a nice illustration of the difference.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • webrunner

    SSC-Dedicated

    Points: 30305

    Luke L (7/26/2010)


    webrunner (7/26/2010)


    Good question, interesting to learn that the parentheses make no difference for SELECT DISTINCT.

    I have one question, though. I've seen people show me queries where they write SELECT COUNT(DISTINCT userid) or something like that. Is that also the same as SELECT DISTINCT COUNT(userid), or is there a difference?

    And I am also curious about how (1) SELECT COUNT(DISTINCT userid) would differ from (2) SELECT COUNT(userid) - I assume if there are multiple rows with the same userid in the table being queried, the second query would return more rows than the first? **

    ** Edited again - sorry, I guess these queries would return the same number of rows (e.g. one summary row), but maybe different COUNT values. Is that correct?

    Thanks,

    webrunner

    AS per The Count() BOL Entry

    The Distinct Keyword is a argument of the Count() Function and is not the same as when used like SELECT DISTINCT col1, col2 from mytable;

    For that use reference the SELECT Clause BOL Entry that states:

    DISTINCT

    -Luke.

    Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

    Thanks, Luke. I also learned two more things from the COUNT BOL entry: (1) "COUNT(*) returns the number of rows in a specified table without getting rid of duplicates" and (2) "For return values greater than 2^31-1, COUNT produces an error. Use COUNT_BIG instead." I hadn't even known about COUNT_BIG before.

    This is all fundamental knowledge that I need to master. It's amazing how many things commonly seen as "basic" or "SQL 101" require careful study to avoid getting tripped up on them.

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • UMG Developer

    SSChampion

    Points: 13482

    Nice question, thanks!

Viewing 15 posts - 1 through 15 (of 26 total)

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