TesteroniPeperoni TesteroniPeperoni - 6 months ago 19
SQL Question

SQL Selecting distinct data

Let's say I have tables called Orders and OrdersInfo. Orders table looks like:

OrderID
1
2
3


And OrdersInfo

OrderID ProductID Amount
1 1 2
1 2 1
2 3 4


I'd like to get all information with the newest OrderID.
In this case the answer should be an information of OrderID 3 which is gonna be empty. My problem is that I have to group it and my information does not show up properly.
I've tried something like this:

SELECT OI.OrderID, OI.ProductID, OI.Amount
FROM OrdersInfo OI
JOIN Orders O
ON OI.OrderID = O.OrderID
GROUP BY OI.OrderID, OI.ProductID, OI.Amount
HAVING OI.OrderID = MAX(O.OrderID)

Answer

You can try this-

SELECT
O.OrderID
,OI.ProductID
,OI.Amount

 FROM
(
SELECT
     MAX(OrderID) AS OrderID
FROM Orders
) AS O
LEFT JOIN OrdersInfo AS OI
OI.OrderID=O.OrderID