Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave M » Distinct clause in Select statement
Add to briefcase
26 posts, Page 1 of 3
123»»»

Distinct clause in Select statement Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
david.moule
Posted Saturday, July 24, 2010 7:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, June 06, 2013 1:14 PM
Points: 72, Visits: 347
Comments posted to this topic are about the item Distinct clause in Select statement
Post #958446
bitbucket-25253
Posted Saturday, July 24, 2010 7:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 5,122, Visits: 20,366
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
Post #958447
ziangij
Posted Monday, July 26, 2010 12:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,552, Visits: 359
good one... thanks
Post #958580
Carlo Romagnano
Posted Monday, July 26, 2010 1:03 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864
Answer is very easy: SELECT DISTINCT does not have parameters.
Post #958603
Duncan Pryde
Posted Monday, July 26, 2010 1:08 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:30 AM
Points: 3,063, Visits: 1,335
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
Post #958606
Christian Buettner-167247
Posted Monday, July 26, 2010 1:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 2,547, Visits: 3,648
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
Post #958613
Andrew Watson-478275
Posted Monday, July 26, 2010 2:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, June 03, 2013 6:20 AM
Points: 954, Visits: 1,878
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".
Post #958649
Daniel Bowlin
Posted Monday, July 26, 2010 6:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 2,681, Visits: 2,423
Thanks. Good question, made me think for a minute or two before answering.
Post #958761
webrunner
Posted Monday, July 26, 2010 8:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:08 PM
Points: 2,121, Visits: 2,226
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

-------------------
"The chemistry must be respected." - Walter White

"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
Post #958813
bitbucket-25253
Posted Monday, July 26, 2010 8:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:57 AM
Points: 5,122, Visits: 20,366
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
Post #958832
« Prev Topic | Next Topic »

Add to briefcase
26 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.