HI All, I am running a query where I am calculating a value called 'YEAR_1_TSB'. I then want to rank the values in column 'YEAR_1_TSB' where records are subsetted into groups by reg.Region and au.BICS.(where each combination of region and BICs are a unique group). I am new to all this and having real difficulty in doing this. Below is a snapshot of the code I am using with the rank() - over - partition. Can somebody help steer me in the right direction please?
SELECT DISTINCT
,ti.Symbol --,au.orgsecid
,au.CompanyID AS varchar(20))
,cp.companyName
,LEFT(au.BICS, 4)
,CG.country
,reg.REGION
,CPB.FYEDATE
,CASE
WHEN DATEDIFF (DAY,CPB.firstpricingdate,CPB.FYEDate)/365 >= 1
THEN ROUND(((pe1.priceCloseISNULL(divadj1.divAdjFactor,1))/(pe2.priceCloseISNULL(divadj2.divAdjFactor,1)))-1,7)*100
ELSE NULL
END AS YEAR_1_TSB
,Select *,
RANK() OVER
(PARTITION BY reg.REGION ORDER BY YEAR_1_TSB
desc) As xRank
Group by firstpricingdate) AS RANKONE