MySQL GROUP by name Group to get the most value one, do not understand Ah, ask for doubts.

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


Reply to discussion (solution)

When the Val maximum is obtained, COUNT (*) is 0, 1>0 is not exactly satisfied

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

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


Thanks for the two-bit moderator answer!
Some understand, but still a little puzzled, when select a.* from Test a where 2 > 0 took 2 and I want to know how this data came out, is to compare with itself? Because I directly write select a.* from Test a where 1>0 this condition is able to take all the data out.

This means traversing each record in the test table and comparing it to itself, and comparing each record with one of its own records.

Equivalent to two times in PHP
foreach ($ar as $v)
foreach ($ar as $VL)

This means traversing each record in the test table and comparing it to itself, and comparing each record with one of its own records.

Equivalent to two times in PHP
foreach ($ar as $v)
foreach ($ar as $VL)


I just think, is a little unsure, haha, thank you two moderators.
  • 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.