Get the newest two line per product and get price and date

Question

I have made a query that extract the two newest lines per product. Each row shows id, productnumber, pricechangedate, price.

Id	Prod number	Date	Price	Rank Order
71582	0071807993	2021-10-15 18:06:22	220.79	1
60533	0071807993	2021-10-15 13:22:46	220.79	2

Is it possible to some how concatenate these rows to show:

Prod number	Newest Date	Newest Price	Second Newest Date	Second Newest Price
0071807993	2021-10-15 18:06:22	220.79	2021-10-15 13:22:46	220.79

My query looks like this:

select * from
(
SELECT
    id,
    prodnumb,
    collectdate, price,row_number() over(partition by prodnumb order by id desc) as rn
FROM product
)A where rn <3

I found out that I can make like this:

select prodnumb, max(collectdate), min(collectdate) 
from
(
SELECT
    id,
    prodnumb,
    collectdate, price,row_number() over(partition by prodnumborder by id desc) as rn
FROM product
-- WHERE deviceId > 0
)A where rn <3
group by prodnumb

Then I get: prodnumber, newest date, second newest date

But how should I do with the prices?

My solution is not recommended. Look at forpas solution below for a better solution:

select p.prodnumb, f.collectdate, f.price, s.collectdate, s.price
from product p

left join (

    select * from
    (
    SELECT
        id,
        prodnumb,
        collectdate, price,row_number() over(partition by prodnumb order by id desc) as rn
    FROM product
    -- WHERE deviceId > 0
    )A where rn = 1
    
    )f on f.prodnumb = p.prodnumb



left join (

    select * from
    (
    SELECT
        id,
        prodnumb,
        collectdate, price,row_number() over(partition by prodnumb order by id desc) as rn
    FROM product
    -- WHERE deviceId > 0
    )A where rn = 2

    )s on s.prodnumb = p.prodnumb
    
group by p.prodnumb

Answer 1

You can do it with MAX(), MIN() and FIRST_VALUE() window functions:

SELECT DISTINCT prodnumb,
       MAX(collectdate) OVER (PARTITION BY prodnumb) NewestDate,
       FIRST_VALUE(price) OVER (PARTITION BY prodnumb ORDER BY collectdate DESC) NewestPrice,
       MIN(collectdate) OVER (PARTITION BY prodnumb) SecondNewestDate,
       FIRST_VALUE(price) OVER (PARTITION BY prodnumb ORDER BY collectdate) SecondNewestPrice
FROM (
  SELECT prodnumb, collectdate, price,
         ROW_NUMBER() OVER (PARTITION BY prodnumb ORDER BY id DESC) rn
  FROM product
) t 
WHERE rn < 3;

Or with conditional aggregation:

SELECT prodnumb,
       MAX(CASE WHEN rn = 1 THEN collectdate END) NewestDate,
       MAX(CASE WHEN rn = 1 THEN price END) NewestPrice,
       MAX(CASE WHEN rn = 2 THEN collectdate END) SecondNewestDate,
       MAX(CASE WHEN rn = 2 THEN price END) SecondNewestPrice
FROM (
  SELECT prodnumb, collectdate, price,
         ROW_NUMBER() OVER (PARTITION BY prodnumb ORDER BY id DESC) rn
  FROM product
) t 
WHERE rn < 3
GROUP BY prodnumb;

You are using ORDER BY id DESC in ROW_NUMBER() of your queries and I kept that in my code, but maybe you should change to ORDER BY collectdate DESC.