I'm trying to run a query to give me the rows that contain the highest 'PRICE COUNT' for each 'YEAR'. This row will contain the 'YEAR', 'ITEM', and 'PRICE' columns.
I've spent some time on this and have hit a wall, even after looking through numerous results on Stack Overflow. I have a query that has come close to what I'm looking for but I'm having trouble figuring out how to return the 'PRICE' column that coincides with the highest value 'PRICE COUNT' column for each 'YEAR', when grouped by 'YEAR', 'ITEM NUMBER', AND 'PRICE'. Below is my query to get me to this point.
SELECT
YEAR(IH.ORDER_DATE) AS 'YEAR',
IL.ITEM_NUMBER AS 'ITEM',
IL.PRICE AS 'PRICE',
COUNT(IL.PRICE) AS 'PRICE COUNT'
FROM
INVENTORY_LINES IL
INNER JOIN
INVENTORY_HEADER IH ON IL.DOC_NUM = IH.DOC_NUM
WHERE
IL.ITEM_NUMBER = 'WIDGET'
AND IL.PRICE > 0
AND IL.AMT_SHIPPED > 0
GROUP BY
YEAR(IH.ORDER_DATE), IL.ITEM_NUMBER, IL.PRICE
ORDER BY
YEAR(IH.ORDER_DATE) ASC, COUNT(IL.PRICE)
Here is the result (The 'PRICE COUNT' column is left in to help better illustrate my point, I don't want it included in the final result):
The rows in the red rectangles are what I want to return instead of all records. The final result would look like this:
Thank you in advance.
EDIT: Zero's answer solved my issue. Thanks Zero, I have accepted your answer.
EDIT 2: Duplicate YEAR data example, look at year 2014.