MySQL Max Count without Order By

I have the following MySQL line:

SELECT age, count(*) AS total FROM pac开发者_JAVA百科ient WHERE age BETWEEN 20 AND 40 GROUP BY age ORDER BY age and I need to add an additional column to it that shows ONLY the max value of the count(*) for every row. For example:

13 2 7

18 2 7

23 5 7

24 7 7

26 6 7

32 3 7

38 1 7

41 1 7

46 4 7

This would be 3 columns and the 3rd column shows 7 since 7 was the highest number in the second column where the count(*) is made.

---

Here the solution:

select age,
    count(*),
    (select max(c) from 
        (select count(*) as c from pacient where age between 20 and 40 group by age) as x) as t 
from pacient 
where age between 20 and 40 
group by age 
order by age;

---

Have you tried to wrap your query with another query? something like

SELECT A.age, A.total, MAX(A.total) as max_value FROM (
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age) as A
GROUP BY A.age, A.total

---

select 
      p.Age,
      count(*)  CountPerAge,
      max(ar.AllRecs) AllRecs
   from
      pacient p,
      ( select count(*) AllRecs 
           from pacient p2
           where p2.age between 20 and 40 ) ar
   where 
      p.age between 20 and 40
   group by
      p.age

By doing a join to the second "subselect" with no join condition, it will give a Cartesian result... Since it is a count with no group by, it will always return a single record and thus be joined to all age rows otherwise. The MAX() of the value is no problem since it is the only record will just be returned as-is.

---

It is always good to use SQL VIEWS instead of using sub queries. Because VIEW will be having already compiled result.

CREATE VIEW subqueryView 
    SELECT age, count(*) AS total 
    FROM pacient
    WHERE age BETWEEN 20 AND 40 
    GROUP BY age ORDER BY age

SELECT A.age, A.total, MAX(A.total) as max_value FROM (SELECT FROM subqueryView) as A
GROUP BY A.age, A.total