January 22, 2015 at 9:28 pm
Comments posted to this topic are about the item Getting Distinct
January 22, 2015 at 11:09 pm
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.
January 23, 2015 at 12:28 am
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
January 23, 2015 at 1:00 am
Extremely easy!
Why do people get it wrong?
I expected 100% of correct answers!
😀
January 23, 2015 at 2:05 am
This was removed by the editor as SPAM
January 23, 2015 at 2:50 am
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.
January 23, 2015 at 2:53 am
Nice question to end the week.
Incorrect answers percentage = 53 % , I hope its count() is working properly 😛
January 23, 2015 at 4:11 am
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?
January 23, 2015 at 4:45 am
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.
January 23, 2015 at 5:14 am
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. 😎
January 23, 2015 at 5:23 am
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:
January 23, 2015 at 5:32 am
Thanks Hugo for a perfectly clear explanation!:-)
January 23, 2015 at 5:38 am
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
January 23, 2015 at 5:46 am
[/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'?
January 23, 2015 at 6:09 am
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;
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply