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

Xavi López · Accepted Answer · 2022-01-26T10:42:42.487

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.

score 5 · Answer 2 · answered May 03 '13 at 09:26

5

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

answered May 03 '13 at 09:26

duffymo

305,152
44
369
561

all coloumns are TEXT datatype. i havent use autonumber for userID – ND's May 03 '13 at 09:27
That's why, then. I wouldn't consider that a very good implementation of anything. – duffymo May 03 '13 at 09:30
Xavi López gives me best answer rather than yours – ND's May 03 '13 at 09:31
He's encouraging you to do something that 'works', but may not be in your best interest in the long run. You'd be better served by modeling your problem correctly in the database. – duffymo May 03 '13 at 09:32
1

@Means duffy is addressing the real issue here. What I provided is a workaround and you should really consider changing your data model. – Xavi López May 03 '13 at 09:33
i cant change database even i know primary key have int datatype but some situation.. – ND's May 03 '13 at 09:34
2

that's fine. maybe the next person who comes along will benefit from knowing that modeling ints as strings has consequences that matter. – duffymo May 03 '13 at 09:35

score 3 · Answer 3 · answered May 03 '13 at 09:32

3

Try to change

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

answered May 03 '13 at 09:32

matzone

5,703
3
17
20

Select MAX INT value of TEXT column

3 Answers3

Linked