CODEWITHSUNDEEP
sqloracle-database
LalitBarpete
LalitBarpete

Reputation: 11

SQL Max Function not returning correct max value

SQL max function not giving the correct result for 

select max (liri_appid) from idea_interface;

result is : 90802 but I know it's having max value as 15454545

The same problem facing when I am running the order by clause. Results are coming as 

 1
 13
 2
 23
 4
 5
 6
 69
 777
 9

Not sure why.

Upvotes: 1

Views: 1625

Answers (2)

APC
APC

Reputation: 146239

LIRI_APPID VARCHAR2(20 BYTE)

liri_appid is defined as varchar2. That means it uses string semantics. ASCII value of 9 is higher than ASCII value of 1 so '90802' is larger than '15454545'.

For the same reason '13' is lower than '2' hence your ordering oddity.

Workaround is

 select max (to_number( (liri_appid))
 from idea_interface;

and

order by to_number( (liri_appid) asc

Proper solution is to change your data model so liri_appid is defined as a number. Using the correct datatype is always the best policy.

Upvotes: 6

Mosius
Mosius

Reputation: 1682

Your field type is definitely a Text type which it order them by their ASCIIcode value like below:

1, 12, 13, 2, 20, 21, ...

Change the field type to a number typed field.

Upvotes: 3

Related Questions