I'm having an issue retrieving the MAX number from database. The catch is that the numbers are in string format since they contain an underscore.

Example case: I have 3 numbers in the database:

123456_1, 123456_2, 123456_13,

The MAX number I would like to fetch is 123456_13 (basically the biggest after underscore), but since they are string, Oracle processes them alphabetically and orders ascending as:

1. 123456_1
2. 123456_13
3. 123456_2

making 123456_2 the MAX.

This would be possible, if I would not need to display the actual MAX or MIN number by replacing the underscore and converting to number:

SELECT
  MAX(TO_NUMBER(REPLACE(number,'_',''))) max_nr,
  MIN(TO_NUMBER(REPLACE(number,'_',''))) min_nr
FROM
...

This would return 12345613 max_nr 1234561 min_nr from my 3 examples, but I need to fetch the actual numbers with underscore and display that.

Is there any way to do this?

CodePudding user response：

You can use REPLACE in ORDER BY statement and FETCH FIRS 1 ROW

select *
from T
order by TO_NUMBER(REPLACE(N,'_','')) desc
fetch first 1 rows only;

SQLize - online editor

CodePudding user response：

You ccould have something like this:

WITH
    tbl AS 
        (
            Select '87789_09' "NBR" From Dual Union All
            Select '9877_1' "NBR" From Dual Union All
            Select '12344_13' "NBR" From Dual Union All
            Select '989898_10' "NBR" From Dual 
        )
Select NBR, Max(To_Number(SubStr(NBR, InStr(NBR, '_')   1))) "PART_NBR" 
From tbl 
Group By NBR 
Order By Max(To_Number(SubStr(NBR, InStr(NBR, '_')   1)))


/* Result:

NBR         PART_NBR
--------- ----------
9877_1             1 
87789_09           9 
989898_10         10 
12344_13          13
*/

With part of column NBR after underscore, separated from itself and converted to number you can further select (you can do it directly too) what you need - Min, Max, whatever...

Regards...

CodePudding user response：

If you want to get the min and max at the same time, you could use the keep syntax twice, ordering by the numeric equivalent of all characters after the underscore:

select
  max(your_number)
    keep (dense_rank last
      order by to_number(substr(your_number, instr(your_number, '_')   1))
    ) as max_number,
  min(your_number)
    keep (dense_rank first
      order by to_number(substr(your_number, instr(your_number, '_')   1))
    ) as min_number
from your_table

MAX_NUMBER	MIN_NUMBER
123456_13	123456_1

fiddle

The instr(your_number, '_') gives the position of the underscore; substr(your_number, instr(your_number, '_') 1) gives you everything after that underscore.

That would work if the values weren't always the same number of digits before the underscore, which could be a problem if you just remove it.