[AccessD] unique patients

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





More information about the AccessD mailing list