Skip to content
Advertisement

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

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:

I found out that I can make like this:

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:

Advertisement

Answer

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

Or with conditional aggregation:

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement