I need to get the highest and lowest value of a certain field to be able to make a filter, only SELECT MAX nor MIN does not work.
As you can see in the image below, I need this amount of users
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?
Basically it's type error.
The string 50 starts with "5"
The string 100 starts with "1"
Min and Max act alphabetically on strings.
To test, change the query to 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 just for testing , ideally you should work with the column of the correct type.
Another good test (just like learning) would be to put the columns with a padding of zeros on the left, and to see that by "coincidence" the strings behave as numbers in the ordering question:
010
050
100
120
etc.
Backup before changes of this type. The query below converts the table column to the correct type, causing its original SELECT to work numerically.
ALTER TABLE prt_license MODIFY users_licensed INTEGER;
After conversion, just do this:
SELECT MAX(users_licensed) FROM prt_license;
Because columns are varchar , sorting is different from numeric columns.
You can cast a column to bring the expected result.
Note that, ideally, this is just for you to see that MIN and MAX function normally. The right thing is to work correctly with the types of columns.
SELECT MIN(CAST(users_licensed AS SIGNED)) FROM prt_license
SELECT MAX(CAST(users_licensed AS SIGNED)) FROM prt_license