I have two tables which I am trying to query.
Stock_Table
- Stock_Code
- Stock_Name
- Physical_Stock
Order_Table
- Order_Date
- Quantity
- Stock_Code
I’m after all stock records where the physical stock is greater than 0 and the last date it was ordered.
I thought the following query would do it but this duplicates the stock records per how many orders there are.
SELECT Stock_Code, Physical_Stock, Order_Date FROM Stock_Table INNER JOIN Order_Table ON Stock_Table.Stock_code = Order_Table.Stock_Code WHERE Physical_Stock <> 0
Advertisement
Answer
Greater than 0 or <> 0 which is it?
You just need to use aggregate function max() to get the max order date since multiple orders could be placed for a given stock code. and since you use aggregation, a group by is needed for the non-aggregated columns.
SELECT Stock_Code, Physical_Stock, max(Order_Date) FROM Stock_Table INNER JOIN Order_Table ON Stock_Table.Stock_code = Order_Table.Stock_Code WHERE Physical_Stock <> 0 GROUP BY Stock_Code, Physical_Stock