Search code examples
sqloledbms-access-2010

Select MAX INT value of TEXT column

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

Solution

  • Your query is returning 3 because it is the largest 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.