Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Distinct clause in Select statement Expand / Collapse
Author
Message
Posted Tuesday, July 27, 2010 3:53 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 8,287, Visits: 8,738
Luke L (7/27/2010)
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.


Just me being stupid. I somehow read it an a totally insane way.


Tom
Post #959762
Posted Sunday, August 01, 2010 3:16 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,794, Visits: 8,009
Carlo Romagnano (7/27/2010)
Hugo Kornelis (7/26/2010)COUNT(*) returns the number of columns (regardless of values);

That's false: COUNT(*) returns the number of ROWS regardless of of columns.

Yes, of course - I typed columns, but intended to write rows. Thanks for catching my mistake.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #961935
Posted Monday, August 02, 2010 7:04 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 10:53 PM
Points: 290, Visits: 714
Explanation: The distinct clause guarantees all output records will be unique, irrespective of how you write the select statement.


 
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


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


John
Post #962155
Posted Monday, August 02, 2010 2:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,794, Visits: 8,009
$partacu$ (8/2/2010)
But while using 2 columns with DISTINCT can produce non unique rows (ateleast in one columm...)

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?)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #962455
Posted Thursday, November 04, 2010 9:04 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Simple
Post #1016373
Posted Wednesday, March 07, 2012 3:02 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 9:09 AM
Points: 483, Visits: 242
Nice and easy question that should result to same answer regardless due to the use of the SELECT DISTINCT. Thanks.
Post #1263315
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse