Group by while discarding old data in query

Question

I have a table (Oracle):

price
--------
integer id (pk)
integer part_id (fk)
number amount
timestamp change_timestamp

And whenever a part price is changed the software (not mine) adds a new row to this table. Instead of altering the data and there being one entry per part, it adds a new row to the table. The software then looks for the latest entry. But the "old" data lingers in the table.

I am trying to write a query that gives me a) the current (latest) price and b) the date that price was entered.

I wrote this query:

select part_id, amount, max(change_timestamp)
from price
group by part_id, amount

But this returns every entry for that part, even the old ones.

How do I return the latest date and the amount for that date while discarding the older, irrelevant data?

No, it's not my database to change or my software to change.

Kaushik Nayak · Accepted Answer · 2018-06-04T04:05:50.973

But this returns every entry for that part, even the old ones.

That's because you are grouping by part_id, amount both, which would consider multiple groups for each part_id, since amount is different for each record. To get just part_id and MAX(timestamp), simply removing amount in group by and select will work. If you want amount as well, then

You could use the MAX analytic function and find the record with that change_timestamp

 SELECT part_id
    ,amount
    ,change_timestamp
FROM (
    SELECT p.*
        ,max(change_timestamp) OVER (PARTITION BY part_id) max_change_timestamp
    FROM price p
    )
WHERE change_timestamp = max_change_timestamp;

Or using row_number which you find already in another answer.

Or using a correlated query:

SELECT part_id
    ,amount
    ,change_timestamp
FROM price  p1
WHERE change_timestamp = (
        SELECT MAX(change_timestamp)
         FROM price p2
        WHERE p2.part_id = p1.part_id
        );

Or LAST ( but not least) aggregate function:

SELECT part_id
    ,MAX(amount) KEEP DENSE_RANK(LAST ORDER BY change_timestamp)
    ,MAX(change_timestamp)
GROUP BY part_id;

Your middle query was the one I went with – ryvantage Jun 04 '18 at 12:46 — ryvantage, Jun 04 '18 at 12:46

score 0 · Answer 2 · answered Jun 04 '18 at 03:47

Use ROW_NUMBER() to find the latest entry of a part_id.

SELECT part_id
    ,amount
    ,change_timestamp
FROM (
    SELECT price.*
        ,ROW_NUMBER() OVER (PARTITION BY part_id ORDER BY change_timestamp DESC) as rn
    FROM price  
    )   
WHERE rn = 1

Group by while discarding old data in query

2 Answers2