MySQL finds the oldest and oldest list of duplicate names

Mysql> Select COUNT (*) as Count  , name,sum (age) as the age of T1 group by name order by Count desc;+-------+--------+- -----+| Count | Name   | Age  |+-------+--------+------+|     3 | Atest  |   | |     2 | Btest  |   Panax Notoginseng | |     2 | CTest  |   | |     2 | Dtest  |   | |     1 | Mary   |   | |     1 | Kou    |   | |     1 | Perter |   | |     1 | Kate   |   |+-------+--------+------+8 rows in Set (0.00 sec)

Here you find the data that count repeats
Next, Count Max, cut the oldest and same data

Mysql> Select Count,name,age from (SELECT COUNT (*) as Count  , name,sum (age) as age from T1 group by name ORDER by C Ount desc, age desc) as TMP GROUP by Count order by Count Desc, age desc;+-------+--------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | Atest  |   | |     2 | CTest  |   | |     1 | Perter |   |+-------+--------+------+3 rows in Set (0.00 sec)

Why is a dtest missing, dtest data and CTest are the same on count and age?

Ask for advice! Thank you

Reply to discussion (solution)

The second type has group by count, then count the same must be in a group of
Since
| 2 | CTest | 43 |
| 2 | Dtest | 43 |
In a group, it's only natural for one to appear.
So the grouping condition should be added to age, which is group by count,age

Mysql> Select COUNT (*) as Count  , name,sum (age) as age from T1 group by name ORDER BY count Desc, ae desc; +-------+ --------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | ZX     |   | |     2 | XZ     |   | |     2 | John   |   | |     2 | Tom    |   Panax Notoginseng | |     1 | Perter |   | |     1 | Mary   |   | |     1 | Kou    |   | |     1 | Kate   |   |+-------+--------+------+8 rows in Set (0.00 sec) mysql> Select Count,name,age from (SELECT COUNT (*) as Count  , Name,sum (age) as of age from T1 Group B name ORDER BY count Desc, age desc) as TMP GROUP by Count,age ORDER by Count Desc, Age desc;+-------+--------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | ZX     |   | |     2 | XZ     |   | |     2 | Tom    |   Panax Notoginseng | |     1 | Perter |   | |     1 | Mary   |   | |     1 | Kate   |   |+-------+--------+------+6 rows in Set (0.00 sec)

That's not a bad result, is it?

The expected result should be:

+-------+--------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | Atest  |   | |     2 | CTest  |   | |     2 | Dtest  |   | |     1 | Perter |   |+-------+--------+------+

Select Count,name,age from (SELECT COUNT (*) as Count, Name,sum (age) as age from T1 Group B
Name ORDER BY count Desc, age desc) as TMP GROUP by count, name order BY count Desc, age desc;

Mysql> Select Count,name,age from (SELECT COUNT (*) as Count  , name,sum (age) as age from T1 group by name ORDER by C Ount desc, age desc) as TMP GROUP by Count,name ORDER by Count Desc, age desc;+-------+--------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | ZX     |   | |     2 | XZ     |   | |     2 | John   |   | |     2 | Tom    |   Panax Notoginseng | |     1 | Perter |   | |     1 | Kou    |   | |     1 | Mary   |   | |     1 | Kate   |   |+-------+--------+------+8 rows in Set (0.00 sec)

GROUP BY Count,name This way you can't find count=2 and Age's largest data and count=1 and Age's largest data ~

+-------+--------+------+| Count | Name   | Age  |+-------+--------+------+|     3 | Atest  |   | |     2 | CTest  |   | |     2 | Dtest  |   | |     1 | Perter |   |+-------+--------+------+

Can this expected result be reflected in a SQL?

Ask for advice

The sense of efficiency should not be very high, although can be made to

(select Count,max (age) as the age from (SELECT COUNT (*)  as Count, Name,sum (age) as the age from T1 group by name ORDER by Cou NT DESC, age desc) as TMP GROUP by Count order by count Desc, age desc) as TEMPD, (select COUNT (*) as Count  , Name,sum (age) As-is-T1 group by name ORDER BY count Desc, age desc) as TMP1 where Tmp1.count=tempd.count and TMP1.AGE=TEMPD . Age count ORDER by tempd.count Desc, tempd.age desc;

Select Tmp1.count,tmp1.age from (select Count,max (age) as the age from (SELECT COUNT (*) as Count, Name,sum (age) as the age from T 1 GROUP BY
Name ORDER BY count Desc, age desc) as TMP GROUP by Count order by count Desc, age desc) as TEMPD, (select COUNT (*) as Co UNT, Name,sum (age) As-is-T1 GROUP by
Name ORDER BY count Desc, age desc) as TMP1 where Tmp1.count=tempd.count and tmp1.age=tempd.age count order by Tmp1.count DESC, tmp1.age desc;

Thank you very much moderator

