mysql max

[Laeg]

tblTest
=============
a | b
=============
1 | 1
1 | 2

select a, max(b) from tblTest
group by b

returns both records

select a, max(b) from tblTest
group by a

returns a single record

How does max work in MySQL???

 

[TonyGroves]

That's exactly how it's supposed to work in standard SQL. If you group by A, then you get a record for each different value of A. If you group by B, you get a record for each different value of B. Try it with a bigger set of records and values and it should become clearer.

 

[r937]

How does max work in MySQL???

exactly the same as it does in every other database system


what's different in mysql is the way GROUP BY works

in any other database system, this is invalid and will give you a syntax error --

select a, max(b) from tblTest
group by b

the reason it's invalid is because there is a column in the SELECT clause which is not an aggregate expression but isn't mentioned in the GROUP BY clause

for more on how GROUP BY works, please read the following two pages:

* GROUP BY and HAVING with Hidden Fields

* Debunking GROUP BY Myths




r937.com | rudy.ca