Query a max cost value, based on max date, distinct to an inventory number

Question

1. I need a query with a desired output shown in bullet #2. Below is a simple query of the data. Notice avgcost can fluctuate for the same date. I need the highest avgcost on the most recent date, distinct to the inventoryno. Can someone help me write a script to do this?

Current query and output

select inventoryno, avgcost, dts
  from invtrans
 where DTS < '01-JAN-23'
 order by dts desc;

INVENTORYNO	AVGCOST	DTS
264	52.36411	12/31/2022
264	52.36411	12/31/2022
264	52.36411	12/31/2022
507	149.83039	12/31/2022
6005	57.45968	12/31/2022
6005	57.45968	12/31/2022
6005	57.45968	12/31/2022
1518	4.05530	12/31/2022
1518	4.05530	12/31/2022
1518	4.05530	12/31/2022
1518	4.15254	12/31/2022
1518	4.15254	12/31/2022
1518	4.1525	12/31/2022
365	0.00000	2/31/2022
365	0.00000	2/31/2022
365	0.00000	2/31/2022

2. My proposed query which doesn't work error: 'not a single-group group function'

 Select distinct inventoryno, Max(avgcost), max(dts)
 from invtrans
 where DTS < '01-JAN-23'
 order by inventoryno;

DESIRED OUTPUT

INVENTORYNO	AVGCOST	DTS
264	52.36411	12/31/2022
507	149.83039	12/31/2022
6005	57.45968	12/31/2022
1518	4.15254	12/31/2022
365	0.00000	2/31/2022

Answer 1

Using row_number analytic function, sort rows per each inventoryno ordered by dts in descending order (so that the most recent rows are sorted first (note that your sample dates are either all the same, or invalid (there's no 31st of February so I modified it to 31st of December for inventoryno = 365)). Then extract rows that ranked as the highest.

SQL> WITH
  2     temp
  3     AS
  4        (SELECT inventoryno,
  5                avgcost,
  6                dts,
  7                ROW_NUMBER ()
  8                   OVER (PARTITION BY inventoryno
  9                         ORDER BY dts DESC, avgcost DESC) rn
 10           FROM invtrans)
 11  SELECT inventoryno, avgcost, dts
 12    FROM temp
 13   WHERE rn = 1;

INVENTORYNO    AVGCOST DTS
----------- ---------- ----------
        264   52,36411 12/31/2022
        365          0 12/31/2022
        507  149,83039 12/31/2022
       1518    4,15254 12/31/2022
       6005   57,45968 12/31/2022

SQL>