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

Source: Internet
Author: User
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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.