Getting Distinct
-
Richard Warr (1/23/2015)
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.
+1
-------------------
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 -
Richard Warr (1/23/2015)
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.
+1
-
Richard Warr (1/23/2015)
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.
+1 Thanks for the question.
Everything is awesome! -
Hugo Kornelis (1/23/2015)
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;
Thanks for the reply Hugo
-
Carlo Romagnano (1/23/2015)
Extremely easy!Why do people get it wrong?
I expected 100% of correct answers!
I miss many of them, I don't google them before answering, nor do I try running them in a query window. This one was a pretty good one as I manually misparsed the select statement as I wasn't focused on that aspect of the question.
Even when I type in programs I believe to be correct, I sometimes get syntax errors.
-
BTW you can get the effect this incorrect query seems to imply:
select * from mytableintersectselect * from mytableGerald Britton, Pluralsight courses
-
Bob Cullen-434885 (1/23/2015)
Thanks Hugo for a perfectly clear explanation!:-)One of the things I appreciate about this site are the people like Hugo that are willing to spend time helping the rest of us learn.
-
This is a nice question. But it needed Hugo's explanation.
Actually it's a very a good question to raise, because maybe it will counteract some of the misleading QotD explanations we've had recently.
Carlo Romagnano (1/23/2015)
Extremely easy!Why do people get it wrong?
I expected 100% of correct answers!
Probably because they are confused about what count(distinct <something>) means. After all, at least two recent QotDs (or their explanations) claim that * makes count "not ignore NULLs", which would mean it was counting values, not rows (there's no such thing as a NULL row, because NULL is about values, not rows), and count(distinct <a value>) is perfectly valid; but count(distinct <something>) is only valid when <something> is a value, not when <something> is "*" and means "all rows"; Without the brackets, count is counting rows and distinct then means distinct rows, whether the row content is expressed as "*" (meaning "all columns" in that context) or as a column list.
Tom
-
This was easy??? Do you actually expect we'll read the question?? I almost put 4 then glanced again and saw it was * not a column.
-
+1 Thanks.
Andre Ranieri
-
. Oops, sorry double posted.
-
That's right, can't do that.
-
Good question...
Back to basics
Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/ -
It WAS easy. Yes, I missed it as well. Sometimes we look for the difficulty in a problem, we miss the easy solution. Point well made, indeed. Just as an aside for all you "why do people get it wrong?" crowd, some people actually wear many hats in their career with SQL being "just" a part of it. Some of us multi-taskers quite often find solutions that even our most "expert" PAID DBAs do not. It's actually kind of funny, because the running joke around the office is that if you want to feel horrible about yourself and your SQL skills, come to this site. That's really too bad. I know that's not what Steve has intended and there is often very valuable information on here. Dismounting soapbox: I would stay and chat more, but I have a ton of work to get to.
-
TriggerMan (2/12/2015)
It WAS easy. Yes, I missed it as well. Sometimes we look for the difficulty in a problem, we miss the easy solution. Point well made, indeed. Just as an aside for all you "why do people get it wrong?" crowd, some people actually wear many hats in their career with SQL being "just" a part of it. Some of us multi-taskers quite often find solutions that even our most "expert" PAID DBAs do not. It's actually kind of funny, because the running joke around the office is that if you want to feel horrible about yourself and your SQL skills, come to this site. That's really too bad. I know that's not what Steve has intended and there is often very valuable information on here. Dismounting soapbox: I would stay and chat more, but I have a ton of work to get to.I sometimes wonder why people are so non-constructive. I made a serious attempt (a few messages back) to answer the "why do people get it wrong" question, because it's an important question and if we can find the answer we can avoid a lot of grief (not only for ourselves, but for others) and now I see a comment like yours which seems to suggest we shouldn't bother. It's not discouraging, really, because for veery comment like yours I see two or three or more that indicate appreciation of what people here are trying to do, but it is really, utterly, shockingly negaitive. Why on earth do you do it?
Tom
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply