Trying to get latest status for related shipment but the results I receive are incorrect

user_16027128 0
Viewed 52

I am currently working on a project while trying to learn MySQL and I would like to join three tables and get the latest status for each related shipment. Here are the tables I'm working with (with example data):

shipments

id consignee tracking_number shipper weight import_no
1 JOHN BROWN TBA99900000121 AMAZON 1 101
2 HELEN SMITH TBA99900000190 AMAZON 1 102
3 JACK BLACK TBA99900000123 AMAZON 1 103
4 JOE BROWM TBA99900000812 AMAZON 1 104
5 JULIA KERR TBA99900000904 AMAZON 1 105

statuses

id name slug
1 At Warehouse at_warehouse
2 Ready For Pickup ready_for_pickup
3 Delivered delivered

shipment_status (pivot table)

id shipment_id status_id
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 1 2
7 2 2
8 3 2
9 4 2
10 5 2

all tables do have created_at and updated_at timestamp columns

Example of the results I'm trying to achieve

slug shipment_id status_id
ready_for_pickup 1 2
ready_for_pickup 2 2
ready_for_pickup 3 2
ready_for_pickup 4 2
ready_for_pickup 5 2

Here's the query I wrote to try to achieve what I'm looking for based on examples and research I did during the past couple of days. I find that sometimes there is sometimes a mismatch with the latest status that relates to the shipment

SELECT
  statuses.slug AS slug,
  MAX(shipments.id) AS shipment_id,
  statuses.id AS status_id,
FROM
  `shipments`
     INNER JOIN `shipment_status` ON `shipment_status`.`shipment_id` = `shipments`.`id`
     INNER JOIN `statuses` ON `shipment_status`.`status_id` = `statuses`.`id`
     GROUP BY
       `shipment_id`
rdbms mysql sql database
2 Answers

Because we need to reference other fields from the same record that evaluates from the MAX aggregation, you need to do it in two steps, there are other ways, but I find this syntax simpler:

SELECT
  shipments.id AS id,
  statuses.slug AS slug,
  statuses.id AS status_id,
  shipment_status.shipment_id as shipment_id
FROM
  `shipments`
     INNER JOIN `shipment_status` ON `shipment_status`.`shipment_id` = `shipments`.`id`
     INNER JOIN `statuses` ON `shipment_status`.`status_id` = `statuses`.`id`
WHERE 
   shipment_status.id = (
       SELECT MAX(shipment_status.id)
       FROM `shipment_status`
       WHERE shipment_status.shipment_id = shipments.id
   )

try it out!

This query makes the assumption that the id field is an identity column, so the MAX(shipment_status.id) represents only the most recent status for the given shipment_id

user_16902170

You can use window functions:

SELECT s.id, st.slug, st.id
FROM shipments s JOIN
     (SELECT ss.*,
             ROW_NUMBER() OVER (PARTITION BY shipment_id ORDER BY ss.id DESC) as seqnum
      FROM shipment_status ss
     ) ss
     ON ss.shipment_id = s.id JOIN
     statuses st
     ON ss.status_id` = st.id
WHERE ss.seqnum = 1;

Also note the use of table aliases so the query is easier to write and to read.

user_11440350
Related