Why does max not even min return the expected value?

Asked

Viewed 354 times

5

I need to take the highest and lowest value of a given field to be able to make a filter, only SELECT MAX or MIN does not work.

As you can see in the image below, I need this amount of users

inserir a descrição da imagem aqui

Table name: prt_license

SELECT MAX(users_licensed) FROM prt_license.

When I run this Query, it returns me 50

SELECT MIN(users_licensed) FROM prt_license.

When I run this Query, it returns me 100

What can I do?

mysql sql
  • 2

    What type of column?

  • 2

    Yeah, now that I’m fixing it, she’s a varchar... she’s automatically generated by the system

  • The result is correct. 5 is greater than 1

  • Only it has fields with 400 users, this would be the biggest

  • 4 is between 1 and 5, ordering by strings would be 100, 3, 400, 50, 9, etc

2 answers

10


Basically it’s a type error.

  • A string 50 begins with "5"

  • A string 100 begins with "1"

Min and Max act alphabetically in strings.

To test, change the query for this: 

SELECT MIN(0 + users_licensed) FROM prt_license

or this:

SELECT MAX(0 + users_licensed) FROM prt_license

So you will be forcing a numerical interpretation. But this is only for testing, ideally you should work with the right type column.

Another good test (just like learning) would be to put the columns with a padding of zeros on the left, and see that there by "coincidence" the strings behave like numbers in the sort question:

010
050
100
120

etc..


To convert the column

Back up before such changes. A query below converts the table column to the correct type, causing your SELECT original work numerically.

 ALTER TABLE prt_license MODIFY users_licensed INTEGER;

After the conversion, this is enough:

 SELECT MAX(users_licensed) FROM prt_license;

  • I didn’t expect this XD

  • What an ingenious solution

  • Solved, thanks a lot

  • @gabrielfalieri the solution I posted now at the end, which is to convert the actual table. SELECT was only for testing.

  • I changed it to integer... better

5

How the columns are varchar, sorting is different from numerical columns.

You can cast a column to bring the expected result.

Note that, ideally, this is just for you to see that the MIN and MAX work normally. It is right to work correctly with column types.

SELECT MIN(CAST(users_licensed AS SIGNED)) FROM prt_license

SELECT MAX(CAST(users_licensed AS SIGNED)) FROM prt_license

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.