Getting Distinct

Question

Getting Distinct

Steve Jones - SSC Editor

SSC Guru

Points: 737981
More actions
January 22, 2015 at 9:28 pm

#75706

Comments posted to this topic are about the item Getting Distinct
January 23, 2015 at 2:05 am

This was removed by the editor as SPAM

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

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 1

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: 259155 More actions · Answer 2

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: 22747 More actions · Answer 3

Extremely easy!

Why do people get it wrong?

I expected 100% of correct answers!

Richard Warr SSCertifiable Points: 6968 More actions · Answer 4

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.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

Nice question to end the week.

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

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 6

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: 64790 More actions · Answer 7

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: 2901 More actions · Answer 8

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 More actions · Answer 9

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 More actions · Answer 10

Bob JH Cullen

SSCrazy

Points: 2082

January 23, 2015 at 5:32 am

#1772563

Thanks Hugo for a perfectly clear explanation!:-)

Thomas Abraham SSChampion Points: 10761 More actions · Answer 11

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 More actions · Answer 12

[/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: 64790 More actions · Answer 13

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/

Getting Distinct

Cookies on SQLServerCentral