split row_number() over partition over multiple columns
10,001
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
Author by
John Henry
Updated on June 27, 2022Comments
-
John Henry almost 2 years
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