0

I am using MySql database and I have the following table oil_order:

oil_order:    

id  supId  ordered_on
1   12     2020-09-01 17:30:00
2   12     2020-09-01 17:30:04
3   12     2020-09-01 17:30:10
4   12     2020-09-01 17:30:13
5   12     2020-09-01 17:30:15

And another table order_item:

order_item:

id  order_id   tankId  amount
1   1           11      54653
2   2           12      54653
3   3           11      54653
4   4           13      54653
5   5           12      54653

One order can have multiple oil_order's. I want to select all records from order_item and for every tank_id where it corresponds with the latest oil_order.ordered_on. For example the result from this will be:

tank_id ordered_on
11      2020-09-01 17:30:10
12      2020-09-01 17:30:15
13      2020-09-01 17:30:13

Order item with tank id 11 has two records, one with order_id 1 and order_id 3. I should return the one with order_id 3 since the record in oil_order with id 3 has bigger date then the one with id 1.

What I have managed to do is:

SELECT item.tank_id,MAX(oil_order.ordered_on)
FROM order_item item
INNER JOIN oil_order oil_order 
ON item.order_id= oil_order.id 
GROUP BY item.tank_id;

But I need to do the following SELECT * from order_item and add the MAX case somewhere lower.

f.trajkovski
  • 794
  • 9
  • 24

1 Answers1

0

You can use window functions, if your database supports them:

select *
from (
    select 
        oi.*,
        row_number() over(partition by oi.tankid order by oo.ordered_on desc) rn
    from order_item oi
    inner join oil_order oo on oo.id = oi.order_id
) t
where rn = 1
GMB
  • 216,147
  • 25
  • 84
  • 135