Retrieving the highest varchar value-CodePudding

We are using the Oracle database. I have a table with the below records

OrderNumber
-----------
JK-PO-01
JK-PO-02
JK-PO-03

Using PL/SQL, I would like to get the latest order number (JK-PO-03)

I tried getting the count of the order group and prefixing count var to JK-PO- but the count_var is always 0.

select count(*) into count_var  from Order where OrderNumber LIKE 'JK-PO-%';

Can someone suggest me approach to get the latest OrderNumber ( JK-PO-03) from the table?

CodePudding user response：

If "the highest" is actually "max", then

SQL> with test (ordernumber) as
  2    (select 'JK-PO-01' from dual union all
  3     select 'JK-PO-02' from dual union all
  4     select 'JK-PO-03' from dual
  5    )
  6  select max(ordernumber)
  7  from test;

MAX(ORDE
--------
JK-PO-03

SQL>

Alternatively, you can sort rows (in descending order) first, and then fetch the one that ranks as the highest:

SQL> with test (ordernumber) as
  2    (select 'JK-PO-01' from dual union all
  3     select 'JK-PO-02' from dual union all
  4     select 'JK-PO-03' from dual
  5    ),
  6  temp as
  7    (select ordernumber,
  8            row_number() over (order by ordernumber desc) rn
  9     from test
 10    )
 11  select ordernumber
 12  from temp
 13  where rn = 1;

ORDERNUM
--------
JK-PO-03

SQL>

Example for the ERS-... set of data:

SQL> with test (ordernumber) as
  2    (select 'ERS-2022-09-02-2003' from dual union all
  3     select 'ERS-2022-09-02-7'    from dual
  4    ),
  5  temp as
  6    (select ordernumber,
  7        regexp_substr(ordernumber, '\d ', 1, 1) num_1,  -- returns 2002
  8        regexp_substr(ordernumber, '\d ', 1, 2) num_2,  -- returns 09
  9        regexp_substr(ordernumber, '\d ', 1, 3) num_3,  -- returns 02
 10        regexp_substr(ordernumber, '\d ', 1, 4) num_4   -- returns 2003 (and 7)
 11      from test
 12    ),
 13  temp2 as
 14    (select ordernumber,
 15       row_number() over (order by to_number(num_1) desc,
 16                                   to_number(num_2) desc,
 17                                   to_number(num_3) desc,
 18                                   to_number(num_4) desc) rn
 19     from temp
 20    )
 21  select ordernumber
 22  from temp2
 23  where rn = 1;

ORDERNUMBER
-------------------
ERS-2022-09-02-2003

SQL>

CodePudding user response：

If the numbers are formatted like in your question you could sort and only show the last

select OrderNumber
from table
order ny OrderNumber desc
limit 1;