[AccessD] unique patients

Thanks David,

i will try both solutions on the large dataset.

I have missed the previous mailings on my topic, because of a holiday i set a stop on the mailings for accessd, and noticed it when i did not received any mailings.
Where there any important mailings on this topic other then the ones below?
Also the archives can't be consulted.

Pedro





Date: Fri, 4 Nov 2011 00:58:48 +0100
From: "Asger Blond" <ab-mi at post3.tele.dk>
To: "'Access Developers discussion and problem solving'"
	<accessd at databaseadvisors.com>
Subject: Re: [AccessD] unique patients
Message-ID: <4480900DEA8F48FCB0796163F3E9C492 at abpc>
Content-Type: text/plain;	charset="utf-8"

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