What is the best way to do a sub query for a max on 2 columns in T-SQL

Question

I have three tables.

• Sales_Header: Sales_Ordernum, Customer, Total Cost
• Sales Details: Sales_Ordernum, Product_number, Sales_Cost
• Product: Product_number, Product_name, Product_height, Product_length

For delivery purposes I want to return the maximum value of Product_height and Product_length could be from multiple products in the product table.

So I would like the output to look like this:

Sales_Ordernum,
Customer,
Max_Productheight,
Max_Productlength

Any help appreciated

Thanks

Answer 1

You can join and aggregate:

select sh.sales_ordernum, sh.customer, 
    max(p.product_height) as max_product_height,
    max(p.product_length) as max_product_length
from sales_header sh
inner join sales_details sd on sd.sales_ordernum = sh.sales_ordernum
inner join product p on p.product_number = sd.product_number
group by sh.sales_ordernum, sh.customer