SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Dave M / Distinct clause in Select statement / Latest Posts

RE: Distinct clause in Select statement

zymos — Wed, 07 Mar 2012 15:02:54 GMT

Nice and easy question that should result to same answer regardless due to the use of the SELECT DISTINCT. Thanks.

RE: Distinct clause in Select statement

Dhruvesh Shah — Thu, 04 Nov 2010 21:04:42 GMT

Simple

RE: Distinct clause in Select statement

Hugo Kornelis — Mon, 02 Aug 2010 14:18:07 GMT

[quote][b]$partacu$ (8/2/2010)[/b][hr]But while using 2 columns with DISTINCT can produce non unique rows (ateleast in one columm...:-))[/quote]As long as there is a difference in at least one column, the rows are not identical.(Or would you describe a blond-haired, blue-eyed guy, aged 34, weighing 95 kg, and measuring 1.95 m as "identical to" a blond-haired, brown-eyed girl, aged 7, weighing 23 kg and measuring 1.23 m, just because they both have blond hair?)

RE: Distinct clause in Select statement

m--S3qU3L — Mon, 02 Aug 2010 07:04:45 GMT

[quote]Explanation: The distinct clause guarantees all output records will be unique, irrespective of how you write the select statement. [/quote][code="sql"] CREATE TABLE TABLE2( Col1 int ,Col2 int)INSERT INTO table2 VALUES(1,3)INSERT INTO table2 VALUES(1,4)SELECT DISTINCT Col1,Col2 FROM table2[/code]I agree the use of parenthesis wont affect the output.But while using 2 columns with DISTINCT can produce non unique rows (ateleast in one columm...:-))

RE: Distinct clause in Select statement

Hugo Kornelis — Sun, 01 Aug 2010 15:16:02 GMT

[quote][b]Carlo Romagnano (7/27/2010)[/b][hr][quote][b]Hugo Kornelis (7/26/2010)[/b]COUNT(*) returns the number of columns (regardless of values);[/quote]That's false: COUNT(*) returns the number of ROWS regardless of of columns. :w00t:[/quote]Yes, of course - I typed columns, but intended to write rows. Thanks for catching my mistake.

RE: Distinct clause in Select statement

L' Eomot Inversé — Tue, 27 Jul 2010 15:53:58 GMT

[quote][b]Luke L (7/27/2010)[/b][hr]Nope, run the code you get 7 and 8 respectively. The Distinct in the second query is applied after the rows are created. So since there is no group by clause it counts all of the userid's and returns 1 row. The DISTINCT in that instance makes sure there are distinct rows, which there are because there is only one. So Yes the DISTINCT keyword doesn't really do anything in the second select, but that was more or less the point of the example that bitbucket gave, to illustrate the difference between Count(DISTINCT expression ) and SELECT DISTINCT count(expression).-Luke.[/quote]Just me being stupid. I somehow read it an a totally insane way.

RE: Distinct clause in Select statement

Carlo Romagnano — Tue, 27 Jul 2010 06:31:25 GMT

[quote][b]Hugo Kornelis (7/26/2010)[/b]COUNT(*) returns the number of columns (regardless of values);[/quote]That's false: COUNT(*) returns the number of ROWS regardless of values of columns. :w00t:

RE: Distinct clause in Select statement

Luke L — Tue, 27 Jul 2010 06:17:00 GMT

[quote][b]Tom.Thomson (7/26/2010)[/b][hr][quote][b]bitbucket-25253 (7/26/2010)[/b][hr]webrunnerEasy enough to find out ... [code="sql"]CREATE TABLE #T(userid INT)INSERT INTO #T SELECT 1 UNION ALLSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 7 SELECT COUNT(DISTINCT userid) AS '(1)' FROM #TSELECT DISTINCT COUNT(userid)AS '(2)' FROM #T[/code]Results:[code="plain"](1) (2) 7 8[/code][/quote]Looks wrong to me. Surely that 8 should be a 1, or the DISTINCT keyword ahould not be present in the second select?[/quote]Nope, run the code you get 7 and 8 respectively. The Distinct in the second query is applied after the rows are created. So since there is no group by clause it counts all of the userid's and returns 1 row. The DISTINCT in that instance makes sure there are distinct rows, which there are because there is only one. So Yes the DISTINCT keyword doesn't really do anything in the second select, but that was more or less the point of the example that bitbucket gave, to illustrate the difference between Count(DISTINCT expression ) and SELECT DISTINCT count(expression).-Luke.

RE: Distinct clause in Select statement

Toreador — Tue, 27 Jul 2010 03:22:39 GMT

A bit of a strange question.I'd never come across the user of parantheses with DISTINCT so assumed it must be some sort of extended syntax I didn't know about. So guessed wrongly that it must have different behaviour.But it turned out that the reason I'd never come across it was that there is no alternative syntax at all!So an interesting problem to think about, but I can't say I learned anything from it (other than that there are apparently people out there who unnecessarily put brackets with a distinct!)

RE: Distinct clause in Select statement

L' Eomot Inversé — Mon, 26 Jul 2010 17:27:18 GMT

[quote][b]bitbucket-25253 (7/26/2010)[/b][hr]webrunnerEasy enough to find out ... [code="sql"]CREATE TABLE #T(userid INT)INSERT INTO #T SELECT 1 UNION ALLSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 7 SELECT COUNT(DISTINCT userid) AS '(1)' FROM #TSELECT DISTINCT COUNT(userid)AS '(2)' FROM #T[/code]Results:[code="plain"](1) (2) 7 8[/code][/quote]Looks wrong to me. Surely that 8 should be a 1, or the DISTINCT keyword ahould not be present in the second select?

RE: Distinct clause in Select statement

Hugo Kornelis — Mon, 26 Jul 2010 14:05:16 GMT

A good question, highlighting a very common misconception. I've seen so many people think that there is a way to limit DISTINCT to only some of the SELECTed columns - and yet, when I ask them what SQL Server should return for the other columns, they are never able to answer. [quote][b]webrunner (7/26/2010)[/b][hr]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?[/quote]COUNT(*) returns the number of columns (regardless of values); COUNT(userid) counts the number of non-NULL vallues in the userid column; and COUNT(DISTINCT userid) counts the number of [i]distinct[/i] non-NULL values in the userid column. This DISTINCT keyword is different from the one at the start of the SELECT list.SELECT DISTINCT .... means that at the end of the query evaluation, rows that are complete duplicates of another row (in all columns) are removed.So SELECT DISTINCT COUNT(userid) FROM SomeTable will first count the number of rows where userid is not NULL, yielding a single row as result (with that number as the value in its only column). The DISTINCT will then remove duplicates - which don't exist as the COUNT without GROUP BY guarantees a single row result set.

RE: Distinct clause in Select statement

UMG Developer — Mon, 26 Jul 2010 13:41:09 GMT

Nice question, thanks!

RE: Distinct clause in Select statement

webrunner — Mon, 26 Jul 2010 09:01:24 GMT

[quote][b]Luke L (7/26/2010)[/b][hr][quote][b]webrunner (7/26/2010)[/b][hr]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[/quote]AS per The [url=http://msdn.microsoft.com/en-US/library/ms175997.aspx]Count() BOL Entry[/url]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 [url=http://msdn.microsoft.com/en-us/library/aa259187.aspx]SELECT Clause BOL[/url] Entry that states: [quote]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.[/quote][/quote]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

RE: Distinct clause in Select statement

webrunner — Mon, 26 Jul 2010 08:56:50 GMT

[quote][b]bitbucket-25253 (7/26/2010)[/b][hr]webrunnerEasy enough to find out ... [code="sql"]CREATE TABLE #T(userid INT)INSERT INTO #T SELECT 1 UNION ALLSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 7 SELECT COUNT(DISTINCT userid) AS '(1)' FROM #TSELECT DISTINCT COUNT(userid)AS '(2)' FROM #T[/code]Results:[code="plain"](1) (2) 7 8[/code][/quote]Thanks - that's a nice illustration of the difference.- webrunner

RE: Distinct clause in Select statement

Luke L — Mon, 26 Jul 2010 08:43:45 GMT

[quote][b]webrunner (7/26/2010)[/b][hr]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[/quote]AS per The [url=http://msdn.microsoft.com/en-US/library/ms175997.aspx]Count() BOL Entry[/url]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 [url=http://msdn.microsoft.com/en-us/library/aa259187.aspx]SELECT Clause BOL[/url] Entry that states: [quote]DISTINCTSpecifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.[/quote]-Luke.Edited to fix quote.

RE: Distinct clause in Select statement

SQLRNNR — Mon, 26 Jul 2010 08:43:05 GMT

Thanks for the question.

RE: Distinct clause in Select statement

bitbucket-25253 — Mon, 26 Jul 2010 08:42:19 GMT

webrunnerEasy enough to find out ... [code="sql"]CREATE TABLE #T(userid INT)INSERT INTO #T SELECT 1 UNION ALLSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3 UNION ALLSELECT 4 UNION ALLSELECT 5 UNION ALLSELECT 6 UNION ALLSELECT 7 SELECT COUNT(DISTINCT userid) AS '(1)' FROM #TSELECT DISTINCT COUNT(userid)AS '(2)' FROM #T[/code]Results:[code="plain"](1) (2) 7 8[/code]

RE: Distinct clause in Select statement

webrunner — Mon, 26 Jul 2010 08:22:46 GMT

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

RE: Distinct clause in Select statement

Daniel Bowlin — Mon, 26 Jul 2010 06:49:03 GMT

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

RE: Distinct clause in Select statement

Andrew Watson-478275 — Mon, 26 Jul 2010 02:56:24 GMT

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

RE: Distinct clause in Select statement

Christian Buettner-167247 — Mon, 26 Jul 2010 01:18:34 GMT

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:[quote]SELECT DISTINCTNULL Dummy,(datepart(year,ModifiedDate)) as year,(ModifiedDate),(1) + (2) from Person.Contactorder by ModifiedDate[/quote]These parentheses are valid, but also superfluous as they provide no additional value.

RE: Distinct clause in Select statement

Duncan Pryde — Mon, 26 Jul 2010 01:08:45 GMT

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

RE: Distinct clause in Select statement

Carlo Romagnano — Mon, 26 Jul 2010 01:03:31 GMT

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

RE: Distinct clause in Select statement

ziangij — Mon, 26 Jul 2010 00:19:08 GMT

good one... thanks :-)

RE: Distinct clause in Select statement

bitbucket-25253 — Sat, 24 Jul 2010 19:02:04 GMT

Nice question - clear and to the point ..yet it makes one think and learnThank you

Distinct clause in Select statement

david.moule — Sat, 24 Jul 2010 19:00:33 GMT

Comments posted to this topic are about the item [B]Distinct clause in Select statement[/B]