Max function not working in simple sql query

Question

I have a table per_all_people_f with 3000-4000 person numbers. A few non-numeric characters are also there in some cases. I want to pick the highest number present in this table. When I am using the below query :

select ( MAX ( DISTINCT person_number) ) from per_all_people_f
where REGEXP_LIKE(person_number  , '^[[:digit:]]+$')

I am not getting the maximum number. I am getting 98098 although much bigger numbers like -1503388 already exist in the system.
How can I change the above query to get the maximum "Number" in this column.

Unrelated, but: distinct doesn't really make sense together with max() — user330315
– user330315, Commented Oct 30, 2019 at 14:40

Gordon Linoff · Accepted Answer · 2019-10-30 14:25:17Z

5

You need to convert to a number. Otherwise the max() is as a string:

select MAX(TO_NUMBER(person_number))
from per_all_people_f
where REGEXP_LIKE(person_number, '^[[:digit:]]+$')

answered Oct 30, 2019 at 14:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Marcus · Accepted Answer · 2019-10-30 15:34:44Z

1

This is conversion issue. You can use below method for conversion:

using CAST:

select MAX(CAST(person_number as NUMBER))
from per_all_people_f
where REGEXP_LIKE(person_number, '^[[:digit:]]+$')

or using TO_NUMBER :

select 
max(TO_NUMBER(person_number))
from per_all_people_f
where 
REGEXP_LIKE(person_number, '^[[:digit:]]+$')

edited Oct 30, 2019 at 15:34

answered Oct 30, 2019 at 14:36

Marcus

4,01130 gold badges78 silver badges125 bronze badges

Collectives™ on Stack Overflow

Max function not working in simple sql query

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