Create a repost of latest and second latest value

Tushar Tambe 0
Viewed 42

I have products audits table looks like this

id product_id column_updated value timestamp
1 product_1 name Big Shoes. "18 September 2022 6:42:50 PM GMT+05:30"
2. product_1 name Green Shoes "18 September 2022 6:42:43 PM GMT+05:30"
3. product_1 name Big Green Shoes "18 September 2022 6:43:43 PM GMT+05:30"

I want to show report of latest change happened on column in form like below

product_id column_updated latest_value previous_value
product_1 name Green Shoes Big Green Shoes

I have prepared a query to fetch last 2 record but not sure how I can merge them to form a view like this?

my query is

select product_id, column_updated, value 
from audits 
where product_id = 'product_1' 
 and column_updated = 'name' 
order by timestamp desc 
limit 2;

Please suggest any approach for this, Thanks in advance!

greatest-n-per-group sql
4 Answers

You need to use LEADa swindow function to hget the latest and previous value

WITH CTE as
(select product_id,
   column_updated,
   value as latest_value,
     lead(value) over (
         partition by product_id,column_updated order by timestamp desc
         ) as previous_value,
       ROW_NUMBER() over (
         partition by product_id,column_updated order by timestamp desc
         ) rn
     
    from audits 
where product_id = 'product_1' 
 and column_updated = 'name')
SELECT product_id, column_updated,latest_value,previous_value FROM CTE WHERE rn = 1
product_id column_updated latest_value previous_value
product_1 name Big Green Shoes Big Shoes. 
SELECT 1

fiddle

nbk0

This problem can be solved by combination of window functions and CTE:

with data as (
  select 
    product_id, 
    column_updated,
    value,
    lag(value) over (partition by product_id, column_updated order by updated_at asc) prev_value,
    row_number() over (partition by product_id, column_updated order by updated_at desc) rn,
    updated_at
 from log
) select 
    product_id, 
    column_updated,
    value,
    prev_value,
    updated_at
from data 
where rn = 1;

online sql editor

where lag give us previous value and row_number give ability to filter only last change

Slava Rozhnev0

You don't need both row_number and lag like the other answers. You can do it with just row_number. Give it a row number and then join back with the prior value having row number 2.

WITH rownumbered AS (
  SELECT product_id, column_updated, value, updated_at
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) rn,
  FROM log
) 
SELECT d.product_id, d,column_updated, d.value, p.value as prevous_value
FROM rownumbered d
JOIN rownumbered p ON d.product_id = p.product_id and p.rn = 2
WHERE d.rn = 1;

You may want to group by product_id and name -- if that is the case it looks like this:

WITH rownumbered AS (
  SELECT product_id, column_updated, value, updated_at
         ROW_NUMBER() OVER (PARTITION BY product_id, column_updated ORDER BY updated_at DESC) rn,
  FROM log
) 
SELECT d.product_id, d,column_updated, d.value, p.value as prevous_value
FROM rownumbered d
JOIN rownumbered p ON d.product_id = p.product_id 
                  and d.column_updated = p.column_updated
                  and p.rn = 2
WHERE d.rn = 1;
Hogan0
Bryan Dellinger0
Related