Getting Distinct

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720963

    Comments posted to this topic are about the item Getting Distinct

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Thank you for the post, Steve, very good one. It was (EP)Z;

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Koen Verbeeck

    SSC Guru

    Points: 258975

    Nice one to end the week, thanks Steve.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Carlo Romagnano

    SSC-Insane

    Points: 22011

    Extremely easy!

    Why do people get it wrong?

    I expected 100% of correct answers!

    😀

  • This was removed by the editor as SPAM

  • Richard Warr

    SSCertifiable

    Points: 6957

    Carlo Romagnano (1/23/2015)


    Extremely easy!

    Why do people get it wrong?

    Not everybody here is an expert. People come here to learn what, to you, may be simple.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • twin.devil

    SSC-Insane

    Points: 22208

    Nice question to end the week.

    Incorrect answers percentage = 53 % , I hope its count() is working properly 😛

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    Hmm. Not sure about this. I tried select distinct * from mytable, and it returned all the rows, which was correct. So you can use * with distinct, just not if you wrap count() round it.

    Why, oh, why, does SQL behave like this?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    Bob Cullen-434885 (1/23/2015)


    Hmm. Not sure about this. I tried select distinct * from mytable, and it returned all the rows, which was correct. So you can use * with distinct, just not if you wrap count() round it.

    Why, oh, why, does SQL behave like this?

    Because the * in SELECT * has a different meaning from the * in COUNT(*), and the DISTINCT in SELECT DISTINCT has a differernt meaning from the DISTINCT in COUNT(DISTINT ...).

    In SELECT *, the * is shorthand for "all columns". This results in a resultset containing all columns. SELECT DISTINCT ... means that rows that have the same value in all columns are returned only once. So * is shorthand for a set of columns, and DISTINCT operates on a set of columns. Works,.

    In COUNT(*), the * is shorthand for "rows". COUNT(*) counts rows, regardless of values. In COUNT(DISTINCT ...), the DISTINCT means that duplicate values are counted only once.. So * in COUNT(*) represents a full row, and DISTINCT in COUNT(DISTINCT ...) operates on single (scalar) values. A row is not a scalar value, so this does not work.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • SqlMel

    SSCrazy

    Points: 2891

    Yes, this was easy for me but only because I ran a query like that a looooong time ago and remembered. 🙂

    Thanks for the question.

    ---------------
    Mel. 😎

  • pmadhavapeddi22

    SSCertifiable

    Points: 5306

    Bob Cullen-434885 (1/23/2015)


    Hmm. Not sure about this. I tried select distinct * from mytable, and it returned all the rows, which was correct. So you can use * with distinct, just not if you wrap count() round it.

    Why, oh, why, does SQL behave like this?

    Hugo Kornelis

    Because the * in SELECT * has a different meaning from the * in COUNT(*), and the DISTINCT in SELECT DISTINCT has a differernt meaning from the DISTINCT in COUNT(DISTINT ...).

    In SELECT *, the * is shorthand for "all columns". This results in a resultset containing all columns. SELECT DISTINCT ... means that rows that have the same value in all columns are returned only once. So * is shorthand for a set of columns, and DISTINCT operates on a set of columns. Works,.

    In COUNT(*), the * is shorthand for "rows". COUNT(*) counts rows, regardless of values. In COUNT(DISTINCT ...), the DISTINCT means that duplicate values are counted only once.. So * in COUNT(*) represents a full row, and DISTINCT in COUNT(DISTINCT ...) operates on single (scalar) values. A row is not a scalar value, so this does not work.

    Hugo Kornelis, SQL Server MVP

    Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

    I know that select count(distinct *) does not work, but i am still not clear on one point,

    The statement could have given the result like (count of all the distinct rows) :unsure:

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    Thanks Hugo for a perfectly clear explanation!:-)

  • Thomas Abraham

    SSChampion

    Points: 10761

    Bob Cullen-434885 (1/23/2015)


    Thanks Hugo for a perfectly clear explanation!:-)

    +1 Still waiting for Hugo to rewrite BOL so they are more useful. 🙂 In his spare time of course.

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    [/quote]

    The statement could have given the result like (count of all the distinct rows) :unsure:

    [/quote]

    So, just for fun - how would you do that if you can't use * to mean 'all columns'?

  • Hugo Kornelis

    SSC Guru

    Points: 64685

    pmadhavapeddi22 (1/23/2015)


    I know that select count(distinct *) does not work, but i am still not clear on one point,

    The statement could have given the result like (count of all the distinct rows) :unsure:

    It could have, if the meaning of language elements in SQL had been defined differently.

    You can still get a count of all the distinct rows by using

    SELECT COUNT(*) FROM (SELECT DISTINCT * FROM xxx) AS x;


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

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

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