I have a query which uses row_number() over partition. When the result comes out it looks like
Product Row_Number Price
A 1 25
A 2 20
A 3 15
B 1 100
B 2 10
B 3 2
I want to get the result to show over columns like
Product Row1 Row2 Row3 price1 price2 price3
A 1 2 3 25 20 15
B 1 2 3 100 10 2
Should I use something like rank()???
I'm using Teradata
You can add two more window functions to get the 2nd and 3rd highest price, this should run in the same STAT-step as your current ROW_NUMBER, so there's no additional overhead:
select
product,
price as Price1,
min(price)
over (partition by product
order by price desc
rows between 1 following and 1 following) as Price2,
min(price)
over (partition by product
order by price desc
rows between 2 following and 2 following) as Price3
from tab
qualify
row_number()
over (partition by product
order by price desc) = 1