SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Distinct clause in Select statement


Distinct clause in Select statement

Author
Message
dave hants
dave hants
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 543
Comments posted to this topic are about the item Distinct clause in Select statement
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16371 Visits: 25280
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

Before posting a performance problem please read
ziangij
ziangij
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 377
good one... thanks :-)
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7652 Visits: 3401
Answer is very easy: SELECT DISTINCT does not have parameters.

I run on tuttopodismo
Duncan Pryde
Duncan Pryde
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4212 Visits: 1552
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
Christian Buettner-167247
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5385 Visits: 3889
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
Andrew Watson-478275
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2221 Visits: 2756
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
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8290 Visits: 2629
Thanks. Good question, made me think for a minute or two before answering.
webrunner
webrunner
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8193 Visits: 4003
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16371 Visits: 25280
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


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search