Why extra subquery needed for filtering out where clause?

Question

First of all. Sorry for the vague title. I couldn't figure out how to frame it. With the sample code, I hope you can understand my doubt.

The task was to find out the top three facilities in a club in terms of revenue generated. I thought this is the final code.

 SELECT x,y as rank
    FROM tableA
 where rank <=3;

But this says the rank column doesn't exist. So I have to include this in another subquery to filter the top 3 ranks.

 SELECT x,rank
    FROM
 (SELECT x,y as rank
    FROM tableA
 ) as sub
 where rank<=3;

Why? I need one extra subquery? Why can't it filter using where clause in the original query?

Code for Facility names, and their rank by revenue:

select res.name,rank() over(order by total desc) as rank
    from
(select fac.facid,fac.name,sum(slots*
                case
                    when memid=0 then guestcost
                    else membercost
                    end) as total
    from cd.bookings bks
    inner join cd.facilities fac
        on bks.facid=fac.facid
    group by fac.facid
order by total desc) as res

What I thought would work:

select res.name,rank() over(order by total desc) as rank
    from
(select fac.facid,fac.name,sum(slots*
                case
                    when memid=0 then guestcost
                    else membercost
                    end) as total
    from cd.bookings bks
    inner join cd.facilities fac
        on bks.facid=fac.facid
    group by fac.facid
order by total desc) as res
where rank<=3;           

     (why this don't work??)

What works :

select name, rank
    from
(select res.name,rank() over(order by total desc) as rank
    from
(select fac.facid,fac.name,sum(slots*
                case
                    when memid=0 then guestcost
                    else membercost
                    end) as total
    from cd.bookings bks
    inner join cd.facilities fac
        on bks.facid=fac.facid
    group by fac.facid
order by total desc) as res) as sub
where rank <=3;

The reason why the first query is not working is because you are using window function directly in the where clause, which is not allowed. But, in the last query , you are doing the ranking within a subquery. — GoonerForLife, Dec 10 '21 at 04:59
You simply can't use a column alias in the WHERE clause on the same level. This is how the SQL syntax was defined. — , Dec 10 '21 at 06:05

score 0 · Answer 1 · answered Dec 10 '21 at 04:55

Consider your first version of the query:

SELECT x, RANK() OVER (ORDER BY y) rnk
FROM tableA
WHERE rnk <= 3;

This is not legitimate, because you can't use aliases in the WHERE clause which were defined in a SELECT at the same level. The issue here is that window functions execute generally execute last, before the filtering in the WHERE clause happens. Instead, you need to compute the rank in a subquery and then filter on it:

SELECT *
FROM
(
    SELECT x, RANK() OVER (ORDER BY y) rnk
    FROM tableA
) t
WHERE rnk <= 3;

Note that some versions of SQL do allow window functions to be used in a QUALIFY clause:

SELECT x, RANK() OVER (ORDER BY y) rnk
FROM tableA
QUALIFY RANK() OVER (ORDER BY y) <= 3;

But Postgres does not support QUALIFY, so the subquery option is what you will likely have to use.

Why extra subquery needed for filtering out where clause?

1 Answers1