How to find Max value of an alphanumeric field in oracle?

Question

I have the data as below and ID is VARCHAR2 type

Table Name :EMP

ID    TST_DATE
A035     05/12/2015
BAB0     05/12/2015
701    07/12/2015
81     07/12/2015

I used below query to get max of ID group by TST_DATE.

SELECT TST_DATE,MAX(ID) from EMP group by TST_DATE;

TST_DATE     MAX(ID)
05/12/2015    BAB0 
07/12/2015    81

In the second row it returning 81 instead of 701.

Answer 1

To sort strings that represent (hex) numbers in numeric, rather than lexicographical, order you need to convert them to actual numbers:

SELECT TST_DATE, ID, TO_NUMBER(ID, 'XXXXXXXXXX') from EMP
ORDER BY TO_NUMBER(ID, 'XXXXXXXXXX');

TST_DATE   ID                TO_NUMBER(ID,'XXXXXXXXXX')
---------- ---- ---------------------------------------
07/12/2015 81                                       129
07/12/2015 701                                     1793
05/12/2015 A035                                   41013
05/12/2015 BAB0                                   47792

You can use that numeric form within your max() and convert back to a hex string for display:

SELECT TST_DATE,
  TO_CHAR(MAX(TO_NUMBER(ID, 'XXXXXXXXXX')), 'XXXXXXXXXX')
from EMP group by TST_DATE;

TST_DATE   TO_CHAR(MAX
---------- -----------
07/12/2015         701
05/12/2015        BAB0

With a suitable number of Xs in the format models of course; how many depends on the size of your varchar2 column.