Distinct clause in Select statement

Question

Post reply

Distinct clause in Select statement

dave hants

Default port

Points: 1485
More actions
July 24, 2010 at 7:00 pm

#235689

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

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

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

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]

ziangij SSCertifiable Points: 7985 More actions · Answer 2

ziangij

SSCertifiable

Points: 7985

July 26, 2010 at 12:19 am

#1198015

good one... thanks 🙂

Carlo Romagnano SSC-Insane Points: 22597 More actions · Answer 3

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

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 4

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

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

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 6

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 7

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

webrunner SSC-Dedicated Points: 31711 More actions · Answer 8

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

webrunner

Easy enough to find out ...

CREATE TABLE #T(userid INT)

INSERT INTO #T

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]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 11

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]

webrunner SSC-Dedicated Points: 31711 More actions · Answer 12

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

webrunner SSC-Dedicated Points: 31711 More actions · Answer 13

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

UMG Developer SSChampion Points: 13482 More actions · Answer 14

UMG Developer

SSChampion

Points: 13482

July 26, 2010 at 1:41 pm

#1198422

Nice question, thanks!