Error when COUNT after MAX ?

Question

Hi, I have a table Act in a medical database :

IDAct   Historic      IDPatient
1       2001-01-01    1
1       2001-01-02    1
2       2001-01-01    2
3       2001-01-03    1

I would like to compute the column IDActPerPatient:

IDAct   Historic      IDPatient  IDActPerPatient
1       2001-01-02    1          1
2       2001-01-01    2          1
3       2001-01-03    1          2

The Act table contains Acts with the historic of every modification of an act. (the index is the pair (IDAct,Historic)). So I'm interested in the last modified acts:

SELECT A.IDActe, MAX(Historic) AS Historic FROM Act A GROUP BY IDAct

Now, I'd like to number the Acts per patient. So I count the number of acts with an IDAct less or equal for one patient. I have created a view LastAct with the previous request and I try this one :

SELECT DA1.*, COUNT(*) AS IDActPerPatient
FROM LastAct DA1
INNER JOIN LastAct DA2 ON DA1.IDPatient = DA2.IDPatient 
AND DA2.IDActe >= DA1.IDAct
GROUP BY DA1.IDAct

...which does not work! I get large numbers in IDActPerPatient when an act has several version in historic (for a patient who has 1 act in 3 versions I have 81).

Do you have an idea where the problem comes from ?

Answer 1

SELECT A.IDActe,
       MAX(Historic) AS Historic,
       (SELECT COUNT(DISTINCT IDAct) FROM ACT B WHERE A.IDPatient=B.IDPatient)
FROM Act A
GROUP BY IDAct

?

Answer 2

Thank you! I don't use select in the select enough but It's really helpful here! here is the fixed request:

SELECT A.IDAct, A.IDPatient, 
       MAX(Historic) AS Historic, 
       (SELECT COUNT(DISTINCT IDAct) FROM Act B WHERE A.IDPatient=B.IDPatient 
        AND A.IDAct>=B.IDAct) AS IDActPerPatient
FROM Act A
GROUP BY IDAct