Using MAX() in VARCHAR Field

Question

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?

http://en.wikipedia.org/wiki/Lexicographical_order – OMG Ponies Jun 15 '11 at 05:35 — OMG Ponies, Jun 15 '11 at 05:35
It's a `varchar` field, why would it be sorted as a number? – Kirk Woll Jun 15 '11 at 05:35 — Kirk Woll, Jun 15 '11 at 05:35

score 30 · Accepted Answer · answered Jun 15 '11 at 05:32

30

You get D010 because alphabetically, D010 comes after D0012 or said another way, D01 comes after D00 and therefore anything that is D01x comes after anything that starts D00x.

answered Jun 15 '11 at 05:32

Thomas

63,911
12
95
141

1

And here is an example, how to deal with such problem -- http://stackoverflow.com/a/16496963/1469208 – trejder Sep 16 '15 at 12:09

score 5 · Answer 2 · answered Jun 27 '16 at 14:35

5

below code is working for me as per your expectation

select max(to_number(regexp_substr(id, '\d+'))) id from <yourtable>;

answered Jun 27 '16 at 14:35

Santhosh

113
1
3
8

score 1 · Answer 3 · edited Aug 21 '22 at 06:31

1

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

edited Aug 21 '22 at 06:31

RF1991

2,037
4
8
17

answered Sep 17 '18 at 05:49

Muhammad ali

287
4
17

score 0 · Answer 4 · answered Apr 20 '16 at 11:03

0

this should work

Select MAX(ID) from table where IsNumeric(ID) = 1 ;

answered Apr 20 '16 at 11:03

Mohamed Islam Fares

193
1
2
10

score 0 · Answer 5 · answered Jan 18 '17 at 20:57

0

This will surely work.

    select MAX(CAST(REPLACE(REPLACE(ID, 'D', ''), '', '') as int)) from <table-name>

answered Jan 18 '17 at 20:57

Ketan Dubey

430
8
19

score 0 · Answer 6 · answered Aug 15 '18 at 08:37

0

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>;

answered Aug 15 '18 at 08:37

BSB

2,270
17
26

Using MAX() in VARCHAR Field

6 Answers6

Linked