SQL Max Function not returning correct max value

Question

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

Not sure why.

LIRI_ID NUMBER LIRI_APPID VARCHAR2(20 BYTE) LIRI_CUST_ID_N NUMBER(15,0) LIRI_PROD_CODE VARCHAR2(8 BYTE) 1 70201801 70201801 A 2 1408001 14080002 A 3 1408002 148003 A 4 1480003 1480003 A 5 1480004 1480004 P 6 1480006 1480006 A 7 1480006 1480007 A 8 1480007 148000701 A 9 1480007 148000702 A 16 210918002 210918002 U 23 25061812 25061812 P 24 25061817 25061817 P 25 25061822 25061822 P 26 25061824 25061824 P 27 25061826 25061826 P 28 26061803 26061803 P 29 26061804 26061804 P 30 26061805 26061805 P 31 26061808 26061808 P 32 26061809 26061809 P — LalitBarpete
– LalitBarpete, Commented Oct 15, 2018 at 7:14
For future reference please edit your question to include clarifications or further information. Code and data are particularly hard to read in comments, due to the lack of formatting. — APC
– APC, Commented Oct 15, 2018 at 7:17

APC · Accepted Answer · 2018-10-15 07:17:44Z

6

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.

edited Oct 15, 2018 at 7:17

answered Oct 15, 2018 at 7:08

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mosius · Accepted Answer · 2018-10-15 07:10:49Z

3

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.

answered Oct 15, 2018 at 7:10

Mosius

1,68225 silver badges36 bronze badges

Collectives™ on Stack Overflow

SQL Max Function not returning correct max value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related