IIRC DISTINCT is not Rushmore aware and will force a table scan. Group by is Rushmore aware so depending on the number of rows in your table and the presence of a matching index your performance mileage will vary. And let's not forget that if the data supports NULLs you may get some different results.
-- rk -----Original Message----- From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Peter Cushing Sent: Friday, January 30, 2015 8:35 AM To: profoxt...@leafe.com Subject: Re: SQL Group By Question Jeff Johnson wrote: > Let's assume I have a select statement: > > SELECT cname, ccity, cnumber sum(1) as groupcount from mytable group > by cname, ccity, cnumber orger by cname > > I have to put all of the fields in the group by but the sum will give > me each individual cname, ccity, cnumber which is not what I want. > > How do I get just the number of cnames? > > VFP9 obviously. > SELECT COUNT(DISTINCT CNAME) FROM MYTABLE Peter _______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/by1pr10mb0216ca6b2851c004d3cc2f95d2...@by1pr10mb0216.namprd10.prod.outlook.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
