Mysql searches for the list of duplicate names with the largest age

Mysql searches for the list of duplicate names with the largest age

mysql> select count(*) as count  ,name,sum(age) as age from t1 group by name order by count desc;+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | atest  |   64 ||     2 | btest  |   37 ||     2 | ctest  |   43 ||     2 | dtest  |   43 ||     1 | mary   |   22 ||     1 | kou    |   22 ||     1 | perter |   23 ||     1 | kate   |   19 |+-------+--------+------+8 rows in set (0.00 sec)

Duplicate count data is found here.
Next, find the largest count, and split the data with the largest age and the same size.

mysql> select count,name,age from ( select count(*) as count  ,name,sum(age) as age from t1 group by name order by count desc ,age desc ) as tmp group by count order by count desc ,age desc;+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | atest  |   64 ||     2 | ctest  |   43 ||     1 | perter |   23 |+-------+--------+------+3 rows in set (0.00 sec)

Why is one dtest missing? the data of dtest is the same as that of ctest on count and age?

Please advise! Thank you.

Reply to discussion (solution)

The second formula has group by count, so the same count must be in one group.
Since
| 2 | ctest | 43 |
| 2 | dtest | 43 |
In a group, only one
Therefore, age should be added to the grouping conditions, that 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     |   64 ||     2 | xz     |   43 ||     2 | john   |   43 ||     2 | tom    |   37 ||     1 | perter |   23 ||     1 | mary   |   22 ||     1 | kou    |   22 ||     1 | kate   |   19 |+-------+--------+------+8 rows in set (0.00 sec)mysql> 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,age order by count desc ,age desc;+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | zx     |   64 ||     2 | xz     |   43 ||     2 | tom    |   37 ||     1 | perter |   23 ||     1 | mary   |   22 ||     1 | kate   |   19 |+-------+--------+------+6 rows in set (0.00 sec)

Is the result incorrect?

Expected results should be:

+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | atest  |   64 ||     2 | ctest  |   43 ||     2 | dtest  |   43 ||     1 | perter |   23 |+-------+--------+------+

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 count desc ,age desc ) as tmp group by count,name order by count desc ,age desc;+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | zx     |   64 ||     2 | xz     |   43 ||     2 | john   |   43 ||     2 | tom    |   37 ||     1 | perter |   23 ||     1 | kou    |   22 ||     1 | mary   |   22 ||     1 | kate   |   19 |+-------+--------+------+8 rows in set (0.00 sec)

Group by count and name cannot find the data with count = 2 and the largest age and the data with count = 1 and the largest age ~

+-------+--------+------+| count | name   | age  |+-------+--------+------+|     3 | atest  |   64 ||     2 | ctest  |   43 ||     2 | dtest  |   43 ||     1 | perter |   23 |+-------+--------+------+

Can this expected result be reflected in an SQL statement?

Please advise

It seems that the efficiency should not be very high, although it can be done

(select count,max(age) as age from ( select count(*) as count  ,name,sum(age) as age from t1 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 count  ,name,sum(age) as age from 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 age from (select count (*) as count, name, sum (age) as age from t1 group
Name order by count desc, age desc) as tmp group by count order by count desc, age desc) as tempd, (select count (*) as count, name, sum (age) as age from t1 group
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 for the moderator.