I have the following sql statement running off a sql 2000 db. Its for ordering stationery. The code is pretty much self explanatory. It needs to get the top 5 items ordered by a user, and must exclude duplicate items. Only problem is I cant seem to exclude the the duplicate items. I added a having clause in there, but it does not seem to work. Any ideas? Want to do this select statement in one sql query...thanks!
Code: Select all
SELECT TOP 5
SI.Item_Code,
SI.Item_ID,
SO.Order_ID,
SOL.Quantity AS Quantity,
SI.Description,
SI.Item_Value,
SI.Quantity AS Stock_Quantity,
SO.Order_Date
FROM
Stationery_Orders SO
INNER JOIN Stationery_Order_Lines SOL ON
SOL.Order_ID = SO.Order_ID
INNER JOIN Stationery_Items SI ON
SI.Item_ID = SOL.Item_ID
WHERE
SO.Employee_Number='1245'
GROUP BY
SI.Item_Code,
SI.Item_ID,
SO.Order_ID,
SOL.Quantity,
SI.Description,
SI.Item_Value,
SI.Quantity,
SO.Order_Date
HAVING
COUNT(SI.Item_ID) = 1
ORDER BY SO.Order_Date DESC
