Is it possible to rank a dataset by the result of another window function in T-SQL?

Question

Is there any way to rank a dataset by the result of another window function?

For example, I have a query like this one below:

select distinct 
    country,
    cast(sum(Sessions) over (partition by country) as float) / cast(sum(sessions) over() as float) as sess_prcnt
from 
    GoogleAnalytics.dbo.SiteVisitsLog
order by 
    sess_prcnt desc

What I want to do is to rank the countries by the sess_prcnt column. Adding a line like rank() over(order by sess_prcnt) or using CTE gives errors.

Thank you in advance!

score 1 · Accepted Answer · answered Sep 19 '20 at 09:59

You say using a CTE gives errors - what sort of errors do they cause? e.g., any issue with doing something like

; WITH A AS
    (select distinct 
        country,
        cast(sum(Sessions) over (partition by country) as float) / cast(sum(sessions) over() as float) as sess_prcnt
    from 
        GoogleAnalytics.dbo.SiteVisitsLog
    )
SELECT *, rank() OVER (order by sess_prct DESC) AS rnk
FROM A
order by 
    sess_prcnt desc

or similar using it as part of the FROM clause

SELECT *, rank() OVER (order by sess_prct DESC) AS rnk
FROM 
    (select distinct 
        country,
        cast(sum(Sessions) over (partition by country) as float) / cast(sum(sessions) over() as float) as sess_prcnt
    from 
        GoogleAnalytics.dbo.SiteVisitsLog
    ) A
order by 
    sess_prcnt desc

You probably also want to ensure you use the appropriate ranking function for your task - ROW_NUMBER, RANK, or DENSE_RANK

Is it possible to rank a dataset by the result of another window function in T-SQL?

1 Answers1