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
CodePudding user response:
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
.