MySQL JOIN for sorting a table with a 1 to many relationship

Question

I may need some hand holding here, my SQL knowledge is enough to get by but not amazing, I'll break it down simple as I can.

I have two tables: orders and shippers
orders has many shippers, related by the orderno column
shippers has a move_dt column (date/time when the order is going to be shipped)

I want to sort orders by the highest move_dt in the shippers table.

In other words: I want to list orders by the date/time they are shipping, and only show each order once.

This query gives me multiple instances of orders, one for each shipper it has:

select `orders`.*, `shippers`.`move_dt` from `orders`
join `shippers` on `shippers`.`orderno` = `orders`.`orderno`
order by `shippers`.`move_dt` desc

What do I need to do so each order shows only once? The query should return the same number of results as select * from orders but be sorted by the highest move date in the shippers table.

I'm happy to post table structures and any other relevant info, and to have edits to my post that make it more clear.

user_2469308 · Answer

Approach 1:

You can Group By on the orderno field; this would result in one row per orderno. Then, you can use Max() aggregation function to get the maximum move_dt value for an order. Eventually, you can sort the result based on the maximum move_dt value.

select o.orderno, -- you can add more columns here from orders table 
       MAX(s.move_dt) AS max_move_dt
from orders AS o 
join shippers AS s on s.orderno = o.orderno
group by o.orderno -- ensure to add extra column from select clause here also
order by max_move_dt desc

Additional Notes:

Do not use Select *; always prefer to use names of the column(s) you want to fetch. Do read: Why is SELECT * considered harmful?
When using Group By, we need to ensure that all the columns/expressions specified in the Select clause are either aggregated expressions, or the columns/expressions specified in the Group By clause. Do read: SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by
It is advisable to use Aliasing when dealing with multi-table queries.

Approach 2:

We can use a Correlated Subquery and fetch the maximum move_dt value for an order. This will do away with the Group by, and Join requirements. Now, you can specify all the column(s) from the orders table in the Select clause, without worrying about specifying them in the Group By clause:

select o.*, 
       (SELECT MAX(move_dt) 
        FROM `shippers` AS s 
        WHERE s.orderno = o.orderno) AS max_move_dt 
from `orders` AS o 
order by max_move_dt desc

MySQL JOIN for sorting a table with a 1 to many relationship

1 Answers