MYSQL why would min be larger than max?
select min(lead), max(lead)
from products
WHERE part_line != 90 and category = 'x'
When I run the above q开发者_运维知识库uery the min returned is larger than the max. If I remove the condition pline != 90 the problem goes away.
I was thinking at first that mysql was processing the min, max before it got to pline... but that doesn't explain why it would assign a higher min.
What gets me, when I get the avg() it calculates correctly.
What's going on?
Maybe your fields are treated as text so, for example 21 will be smallest than 3 (alphabetical order) If that fields are type of TEXT try to cast values to INT()
Here is a sql server example:
with Test_CTE (first, second, third)
as(
select '21 ', 'b', 1
union
select '3 ', 'd', 2
)
select MIN(first) as Minimum, MAX(first) as Maximum
from Test_CTE
output is
Minimum Maximum
21 3
Note that the values are Text data with trailing spaces, hence the reason it's sorted differently from how you might expect.
精彩评论