TABLE DOESN'T EXIST - CANNOT REFERENCE SUBQUERY TABLE FROM OTHER SUBQUERY-CodePudding

I am using mysql. I have a database with tables medico, paziente and visita (doctor, patient and visit respectively). I want to get the dates with the maximum number of visits in one day. So I create a subquery that returns the dates with the number of visits of that day, and then filter them out to get only the ones with the max number of visits in one day. This should work, but the subquery after the WHERE tells me it doesn't find the table named R, that is the table result of the subquery before. This is the entire query:

SELECT R.Data, 
       R.Conto 
FROM  ( SELECT Data, 
               COUNT(*) AS Conto 
        FROM visita 
        GROUP BY Data
       ) AS R 
WHERE R.Conto = ( SELECT MAX(R.Conto) FROM R );

Can anyone explain why can't I reference the table from the other subquery and how should I approach this problem the correct way?

CodePudding user response：

A lot easier to grasp if you have version 8 or above using cte

DROP table if exists t;

create table t
(data date);
insert into t values
('2022-09-01'),('2022-09-01'),('2022-09-02'),('2022-09-03'),('2022-09-03');

with cte as
(select data,count(*) cnt from t group by data)
,
cte1 as
(select max(cnt) maxcnt from cte)
select data,cnt 
from cte 
cross join cte1 
where  cnt = maxcnt;

 ------------ ----- 
| data       | cnt |
 ------------ ----- 
| 2022-09-01 |   2 |
| 2022-09-03 |   2 |
 ------------ ----- 
2 rows in set (0.002 sec)