Select within a group - redux

[scottshiv]

I need to select 75 patients for each doctor in my database and with your help I got that working. The query results delivered 75 patients for each doctor. Now I find out that the requestor wants to select 75 random patients.

There are 4 doctors in the database. Each doctor has several hundred patients. when I run my randomized query, it returns sometimes 290 rows, sometimes 314 rows, etc. It never returns at least 75 patients for each doctor.

Table name = Medmatch
TPI = Doctor ID
PCN = Patient ID

Here is my code:
SELECT D1.*
FROM Medmatch AS D1
WHERE ((D1.PCN) In (Select top 75 PCN From Medmatch as D2 Where D1.TPI = D2.TPI
Order by Rnd(Asc(D2.PCN))))
ORDER BY D1.TPI, D1.PCN;

I even got desperate and tried Rnd(D1.PCN)instead of D2.PCN and that didn't help.

Any suggestions?

Thank you

 

[Golom]

Try this

SELECT D1.*

FROM Medmatch AS D1

WHERE ((D1.PCN) In 

(Select TOP 75 PCN From 

   (Select PCN, Rnd(PCN) As R From MedMatch D3
    Where D3.TPI = D1.TPI) As XX

Order By XX.R ) As D2

ORDER BY D1.TPI, D1.PCN;