Scenario:
I have a table with order status, for example:
/ ORDER LOG NUMBER LOG CODE
1 1 1 Preparing
2 1 2 Prepared
3 1 3 Sent
4 2 1 Preparing
5 2 2 Prepared
6 3 1 Preparing
I've been looking for a way to select orders, where last log code is Prepared.
For example I want to see all ORDERS where last LOG CODE is Prepared (last log)
Oracle supports Windowed Aggregates:
select *
from
( select
ORDER
,LOG_NUMBER
,LOG_CODE
-- last log number for each order
,max(LOG_NUMBER) over (partition by ORDER) as maxnum
from mytable
) dt
where LOG_NUMBER = maxnum
and LOG_CODE = 'Prepared'
or
select *
from
( select
ORDER
,LOG_NUMBER
,LOG_CODE
-- youngest row gets row_number 1
,ROW_NUMBER() over (partition by ORDER order by LOG_NUMBER desc) as rn
from mytable
) dt
where rn = 1
and LOG_CODE = 'Prepared'
You can use the analytic function to do so
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions070.htm
It should be something like:
SELECT order LAG(log_code, 1, 0) OVER (PARTITION BY order ORDER BY log_number) AS prev_code FROM orders
This will at least deliver you a resultset which has the last code information.
Instead of using an outer select you should be able to extend the query with
"having prev_code = 'Prepared'"
A pretty efficient way is to use correlated subqueries:
select t.*
from t
where t.lognumber = (select max(t2.lognumber) from t t2 where t.order = t2.order) and
t.logcode = 'Prepared';
