CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Femme Fatale
Femme Fatale

Reputation: 880

SQL MAX() Function

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
1
2
3
4
5
6
9
99
909
 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?

Upvotes: 3

Views: 11473

Answers (3)

thezboe
thezboe

Reputation: 552

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.

Upvotes: 5

Khan
Khan

Reputation: 18142

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.

Upvotes: 4

user359040
user359040

Reputation:

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

Upvotes: 11

Related Questions