Is a join necessary in this sql query?

Question

Suppose I have a table Orders in sql with 4 columns (OrderId int, ProductID int, Price money, Size int) with OrderID and ProductID as a joint primary key.

If I want a query to return the most recent order (along with price and size) for every product (i.e., maximal OrderId) is there a way to do this without a join?

I was thinking of the following:

select o.OrderId, o.ProductId, o.Price, o.Size
from Orders o inner join 
(select Max(OrderId) OrderId, ProductId from Orders group by ProductId) recent
on o.OrderId = recent.OrderId and o.ProductId = recent.ProductId

but this doesn't seem like the most elegant solution possible.

Also, assume that an order with multiple products generates multiple rows. They are both necessarily part of the primary key.

Answer 1

select
 MAX(o.OrderId) as NewestOrderID,
 o.ProductId,
 o.Price,
 o.Size
from Orders o
Group by
 o.ProductId,
 o.Price,
 o.Size

Answer 2

But if OrderID and ProductID are your PK as you state then it doesn't matter if it's all distant because ProductID will never repeat within an OrderID.

Answer 3

No, there isn't. You've got the right idea. You need to do the subquery to get the max(orderID), productID pair, and then join that to the full table to limit the query to the rows in the full table that contain the max OrderId.