Clever Geek Handbook

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

+3


source to share


3 answers


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.



+5


source


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
+9


source


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.

+3


source






More articles:


All Articles