CODEWITHSUNDEEP
sql
chuck taylor
chuck taylor

Reputation: 2516

Is a join necessary in this sql query?

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.

Upvotes: 0

Views: 434

Answers (2)

Drew McGhie
Drew McGhie

Reputation: 1086

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.

Upvotes: 2

Sage
Sage

Reputation: 4937

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

Upvotes: 0

Related Questions