0

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):

Current Result

The rows in the red rectangles are what I want to return instead of all records. The final result would look like this:

Final Result

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.

Example Image

1 Answers1

0

As per the given query, you final query will be something like this.

WITH inter_table (year_of_order, item_number, price, price_count) AS (
SELECT 
    YEAR(IH.ORDER_DATE) AS year_of_order,
    MAX(IL.ITEM_NUMBER) AS item_number,
    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.PRICE
)

SELECT
    i1.*
FROM
    inter_table i1
INNER JOIN
    (
        SELECT
            year_of_order, MAX(price_count) as price_count
        FROM
            inter_table
        GROUP BY
            year_of_order
    ) i2
ON i1.year_of_order = i2.year_of_order AND i1.price_count = i2.price_count;
Zero
  • 1,807
  • 1
  • 6
  • 17
  • You're right, you just forgot to close the subquery – Noubar K. Jul 03 '23 at 19:25
  • My bad this is T-SQL, Thought it was SQL server for a sec – Noubar K. Jul 03 '23 at 19:26
  • I left an edit above in my question with a link. Sorry, I couldn't format it better in a comment back to you. – StoneOcean Jul 03 '23 at 19:39
  • @Zero I made the order by change but the returned results don't match what's in the final results image I have attached above. Some values are off. – StoneOcean Jul 03 '23 at 19:43
  • Sorry @StoneOcean, I thought it needed to be grouped by `price_count`, I was trying with `price` earlier. I have updated the query now and also changed some column names, but it'll be understandable. – Zero Jul 03 '23 at 20:00
  • @Zero This works great but can show multiple results for each year where the PRICE_COUNT has a match. Would you know how to restrict the results to only show one results per year and have it be the one with the highest PRICE? – StoneOcean Jul 05 '23 at 17:03
  • It shouldn't show multiple results for each year since it groups on the year. – Zero Jul 05 '23 at 17:10
  • @Zero I have another Item instead of WIDGET that I'm using and I – StoneOcean Jul 06 '23 at 12:05
  • Ohh okay, then you add widget in the GROUP clause again, based on this data, I assumed it is `WIDGET` only – Zero Jul 06 '23 at 12:06
  • @Zero I'm sorry, my last comment was cut off... please ignore what I said and I'll explain from the beginning. Your answer is what I'm looking for as it is. The only issue I am seeing is that if the records for a particular year have values in the PRICE_COUNT column that match (and are the highest values) then those two competing records will show up in the results of the query in your answer. I ran a different item number against this query that showed this behavior. I am wanting the highest PRICE of all of the competing records for the particular year. – StoneOcean Jul 06 '23 at 12:22
  • @Zero I do appreciate your help with this. – StoneOcean Jul 06 '23 at 12:25
  • @Zero I added an image to my original post showing the result of using WIDGET2 as my item instead of WIDGET. The year 2014 is where the competing records show. – StoneOcean Jul 06 '23 at 12:46
  • Better if you create another question others can help as well since this one's closed. – Zero Jul 06 '23 at 13:08
  • As for your results, it seems two results have the same `year` and `price_count`. If you need only a record for that, `RANK`, `DENSE_RANK`, or `ROW_NUMBER` functions to fetch only one record based on some condition. – Zero Jul 06 '23 at 13:11