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?
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>