MySQL GROUP by name group to the maximum value of one, do not understand AH seek doubts. Solution Ideas

Source: Internet
Author: User
MySQL GROUP by name Group to get the most value one, do not understand Ah, ask for doubts.
Select A.* from Test a where 1 > (select COUNT (*) from test where name = A.name and val > A.val)
SQL statement, the field that displays the largest Val value by name group
Issue: SELECT COUNT (*) from test where name = A.name and val > a.val subquery is not a numeric value detected? The SQL statement doesn't have to be.
Select A.* from Test a where 1 >1? But this logic is obviously wrong, what is the right logic? How are the outer and inner layers compared? It's not enough to understand the IQ.
------to solve the idea----------------------
When the Val maximum is obtained, COUNT (*) is 0, 1>0 is not exactly satisfied
------to solve the idea----------------------
When the a.val is the maximum value
Val > A.val not set, (SELECT COUNT (*) from test where name = A.name and val > A.val) return 0 (no qualifying record)
1 > (select COUNT (*) from test where name = A.name and val > A.val)
Established, the record was elected

You already know, but you didn't get around bend.
Select A.* from Test a where 1 > Count
Count is the number of records that are greater than a.val, and only conut equals 0 when it does not exist. Expression 1 > 0 established
  • Related Article

    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.