开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜