Asger Blond
ab-mi at post3.tele.dk
Thu Nov 3 18:58:48 CDT 2011
Excellent David! As to performance, testing the two queries in SQL Server shows the same execution plan (which doesn't necessarily mean that the plan and cost would be the same with a different and bigger dataset - but I would guess not). Asger -----Oprindelig meddelelse----- Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af David McAfee Sendt: 3. november 2011 23:43 Til: Access Developers discussion and problem solving Emne: Re: [AccessD] unique patients Wow, my eyes or mind must have filtered that second line out when typing the records in. SELECT * FROM tblSomeTable ORDER BY Uitv, Sub, Patient PKID Uitv Sub Patient ----------- ---- ---- ----------- 1 A ee 101 8 A ee 101 2 A ee 102 3 A ff 201 4 A gg 301 5 B ee 201 6 B ee 301 7 B hh 501 (8 row(s) affected) SELECT Uitv, Sub, Patient FROM tblSomeTable GROUP BY Uitv, Sub, Patient ORDER BY Uitv, Sub, Patient Uitv Sub Patient ---- ---- ----------- A ee 101 A ee 102 A ff 201 A gg 301 B ee 201 B ee 301 B hh 501 (7 row(s) affected) --Either of these work the same, not sure which is faster/better: SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient FROM (SELECT Uitv, Sub, Patient FROM tblSomeTable GROUP BY Uitv, Sub, Patient) A GROUP BY Uitv, Sub ORDER BY Uitv, Sub Uitv Sub UniquePatient ---- ---- ------------- A ee 2 A ff 1 A gg 1 B ee 2 B hh 1 (5 row(s) affected) SELECT Uitv, Sub, COUNT(Patient) AS UniquePatient FROM (SELECT DISTINCT Uitv, Sub, Patient FROM tblSomeTable) A GROUP BY Uitv, Sub ORDER BY Uitv, Sub Uitv Sub UniquePatient ---- ---- ------------- A ee 2 A ff 1 A gg 1 B ee 2 B hh 1 (5 row(s) affected) On Thu, Nov 3, 2011 at 3:24 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote: > WIth the original dataset, not the cleansed one? > > -- > Stuart > -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com