I found a very interesting/strange thing about MAX() function in SQL.
I have column ID with varchar2(20) data type, having following entries:-
ID
As per my understanding if i use "select max(ID) from table;" I should get 909 as the result but i get 99. Can somebody explain why this is happening?
1
2
3
4
5
6
9
99
909
Add a comment
|
3 Answers
You have misunderstood - since the column is a varchar, not numeric, it is sorted by string values; 909 comes before 99, so 99 is the maximum.
To see the maximum numeric value of your column, try:
select max(to_number(ID)) from my_table
3 Comments
DOK
Could you suggest SQL that would perform the conversion and yield the answer? Assuming that we can't always just change the database schema when we want to.
Femme Fatale
Can i please understand the mechanics... some reference would really help me.
Since the column you are using MAX on is of type VARCHAR, it is going to sort the values based on a character-by-character evaluation. It selects 99 because 9 > 0, and it will ignore the rest of the string.
Comments
Your column is being represented as characters, not numbers. So think of it as ordering these alphabetically. Alphabetically 909 will come before 99 in ascending order.
