Ranking in Teradata- Sql
I Am Trying to Rank My Sales Data Using the Rank() over Function. Here Is My Code: Select Category as Cat, Units*Cost as Cost_Sales, Units*Retail as...
I am trying to rank my sales data using the rank() over function . Here is my code :
Select
Category as CAT
,units*cost as COST_SALES
,units*retail as RETAIL_COST
,units as UNITS_SOLD
,RANK() OVER (PARTITION BY 1 ORDER BY 3 DESC ) AS RANKING
from Table
Where date between current_date-7 and current_date
group by 1
When I get my result it is unordered and shows rank 1 for all the categories.
1 Answer
You can't use column references in the window functions. You need to name the columns explicitly:
Select Category as CAT, units*cost as COST_SALES, units*retail as RETAIL_COST,
units as UNITS_SOLD,
RANK() OVER (PARTITION BY Categroy ORDER BY units*retail DESC ) AS RANKING
from Table
Where date between current_date-7 and current_date
group by Category;
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
