Select MAX INT value of TEXT column

Question

UserID    UserName  Password

1                abc               123
10               xyz               456
3                mno               254

SELECT MAX(UserId) AS UserId FROM UserLogin

When I run this query it gives me 3 instead of 10

All columns are TEXT datatype

Answer 1

Try to change

SELECT MAX(val(UserId)) AS UserId FROM UserLogin

Answer 2

Your query is returning 3 because it is the larger value considering lexicographic order (anything starting with 3 is considered greater than anything starting with 1, just like something starting with b is greater than anything starting with a).

Use the VAL function to convert the TEXT columns into numeric values:

SELECT MAX(VAL(UserId)) AS UserId FROM UserLogin

If you're concerned about performance, you should make this column numeric, though. Take into account you're calling a function for every row in the table. Also, it won't be using any indexes this column may have.

Answer 3

Make sure that the column type is numeric and not varchar or string.