How to get latest date of specific record without specifying record?

Question

I have 2 tables: order and transportation.

ORDER  
id

TRANSPORTATION  
id  
order_id  
date  
status            (could be 'ok', 'ca', 'ko')

1 order can have more than 1 transportation. I want all orders which its latest transportation status is 'OK'.

If I do:

select ord.*
from   orders ord
       join transportation tr
       on ord.id = tr.order_id
where  tr.date = (select max(date) from transportation where status like 'OK');

I will get the latest date of ALL transportations but I only want the latest date of all transportations of that order in specific.

For example, if I have these orders with these transportations and I want the last transportations of each order which status are 'ok':

order_id, transportation_id, date, status
001, 001, 01/01/19, ok
001, 002, 01/01/20, ca

002, 003, 01/01/19, ca
002, 004, 01/01/18, ok

003, 005, 01/01/17, ok
003, 006, 01/01/16, ca

I would expect these results:

003, 005, 01/01/17, ok

Answer 1

You can do it without an additional sub-query using an analytic query:

SELECT order_id,
       transportation_id,
       "DATE",
       status
FROM   (
  select ord.id AS order_id,
         tr.id AS transportation_id,
         tr."DATE",
         tr.status,
         RANK() OVER ( PARTITION BY ord.id ORDER BY tr."DATE" DESC ) AS rnk
  from   orders ord
         join transportation tr
         on ord.id = tr.order_id
)
WHERE  rnk = 1
AND    status = 'ok';

Use RANK (or DENSE_RANK) if you want to return rows tied for the greatest date per order id; or use ROW_NUMBER if you only want a single row per order id.

So for your test data:

CREATE TABLE ORDERS ( id ) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

CREATE TABLE TRANSPORTATION ( order_id, id, "DATE", status ) AS
SELECT 001, 001, DATE '2001-01-19', 'ok' FROM DUAL UNION ALL  
SELECT 001, 002, DATE '2001-01-20', 'ca' FROM DUAL UNION ALL
SELECT 002, 003, DATE '2001-01-19', 'ca' FROM DUAL UNION ALL
SELECT 002, 004, DATE '2001-01-18', 'ok' FROM DUAL UNION ALL
SELECT 003, 005, DATE '2001-01-17', 'ok' FROM DUAL UNION ALL
SELECT 003, 006, DATE '2001-01-16', 'ca' FROM DUAL;

This outputs:

ORDER_ID | TRANSPORTATION_ID | DATE                | STATUS
-------: | ----------------: | :------------------ | :-----
       3 |                 5 | 2001-01-17 00:00:00 | ok    

db<>fiddle here

Answer 2

DO it with not exists

select ord.* from orders ord join transportation tr on ord.id = tr.order_id
where tr.status = 'OK'
and not exists(select 1 from transportation b where b.status = 'OK' and tr.date > b.date)