CODEWITHSUNDEEP
sqloracle-database
SSA_Tech124
SSA_Tech124

Reputation: 599

Max function not working in simple sql query

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.

Upvotes: 0

Views: 82

Answers (2)

Marcus
Marcus

Reputation: 3869

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:]]+$')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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:]]+$')

Upvotes: 5

Related Questions