How to 'Group By' a value that is not constant?

Scenario: I need to make a query to show the most recent value($) for all of my items. Note, the value of these items changes over time, so I would like to see what the most current value is for that item. I have simplified my tables for this example, but basically I have the below 3 tables I need to use for this query:

Table 1:(This table is simply to get the ITEM_name based on it's ID)

Table 2: (this table reflects the price of an item and tracks how the value of that items changes over time)

Table 3: (This table contains additional information on my items that I would like in my final output)

Expected Results: As my end result, I would like to see the item name along with its most current value we have on record (Note, I am filtering by the max end date to get the most current item value)

Below Query Result:(the 2nd record should not show in my final output)

My Query:

select M.ITEM_Name,MAX(MMM.End_Date), MMM.Item_Value($),MM.ITEM_Owner
FROM Table_3 MM
    INNER JOIN Table_1 M ON M.ITEM_ID = MM.ITEM_ID
    INNER JOIN Table_2 MMM ON MMM.ITEM_ID = MM.ITEM_ID
GROUP BY M.ITEM_Name,MAX(MMM.End_Date), MMM.Item_Value($),MM.ITEM_Owner;

From my understanding, there is an issue with my group by clause.It is trying to group by Item_Value($) column as well, even though those are not constant values. I need my result to only show the Item_Value($) for the Max end date, and not both end dates. Any suggestions on how I could achieve this?

Yes very obviously there is an issue with your GROUP BY clause. With GROUP BY x, y, z you say you want one result row for each x, y, z. You want one result row per item, so GROUP BY item_id or GROUP BY item_name.

If you GROUP BY m.item_name, mmm.item_value, then you get one row for each item and price naturally. And you cannot GROUP BY MAX(mmm.end_date), because MAX(mmm.end_date) is the maximum end date for the group you state in GROUP BY. You cannot say give me the maximum date for the maximum date; it just makes no sense.

Your sample data seems incorrect, too, because for item 101 you have two overlapping ranges, which would mean that in 06/2022 for instance the item had two different prices at the same time. Or do you want to allow multiple prices for a period and show them all in your results, then?

Well, as you want the row with the greatest end date anyway (so you don't expect future entries in the table or you don't care that you may sjhow future prices instead of current ones), this won't be an issue here. You could use Oracle's KEEP LAST to get an item's last price, or better yet, use either a window function or a lateral join.

You have three tables

• table 1, which contains one row per item, so I am calling this the item table.
• table 2, which contains the prices with one row per item and time range, so I am calling this the price table.
• table 3, which seems to contain one row per item, too, so I wonder why this is a separate table at all. Why don't you store the information in the item table? Well, I am calling this the item_plus table.

The lateral join solution (CROSS APPLY):

SELECT
  i.item_name,
  lp.end_date,
  lp.item_value,
  ip.item_owner
FROM item i
INNER JOIN item_plus ip ON ip.item_id = i.item_id
CROSS APPLY
(
  SELECT *
  FROM price p
  WHERE p.item_id = i.item_id
  ORDER BY p.end_date DESC
  FETCH FIRST ROW ONLY
) lp
ORDER BY i.item_name;

If you want a solution with a window function, look for similar requests in stackoverflow. You'd want MAX(end_date) OVER (PARTITION BY item_id) or use ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY end_date DESC).