SQL MAX () Function
I found a very interesting / strange thing about the MAX () function in SQL.
I have a column ID with datatype varchar2 (20) having the following entries: -
I WOULD
According to my understanding, if I use "select max (ID) from table"; I should get 909 result, but I get 99. Can someone explain why this is happening?
1
2
3
4
5
6
9
99
909
Since the column you are using MAX on is of type VARCHAR, it will sort the values based on the score for each character. It selects 99 because 9> 0 and it will ignore the rest of the line.
You misunderstood - since the column is varchar, not numeric, it is sorted by string values; 909
precedes 99
, therefore 99
maximum.
To see the maximum numeric value of a column, try:
select max(to_number(ID)) from my_table
Your column is represented as characters, not numbers. So think about it by sorting them alphabetically. Alphabetically, 909 will be up to 99 in ascending order.