Distinct clause in Select statement

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

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

  • good one... thanks 🙂

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

  • 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

  • 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

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

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

  • 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

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

  • 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

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

  • 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

  • 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

  • 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