Using MAX() in VARCHAR Field

user_683518 0
Viewed 44713

I have a table with following set of data

ID (VARCHAR2 field)
D001
D002
D010
D0012

I use max() in this field.

Select max(ID) from <table-name>;

It returns D010 as result.

Why is the result not D0012?

sorting oracle string
6 Answers

First Varchar need to Casted as int to select as MAX. Use below query:

select max(CAST(ID as signed)) as max_id from <table-name>;

user_67151770 
SELECT * 
FROM  `<TABLE_NAME>` 
ORDER BY CAST(  `ID` AS DECIMAL( 10, 3 ) ) DESC
user_67736140
Related