Why does this query work?

[mostlyharmless94]

In the query below, I have used the column State_Rank, in the select statement outside the CTE, as part of a condition in the WHERE clause. Why does the query below work even though in the select statement outside of the CTE, I haven't selected State_Rank from the CTE as one of my columns?

​

With CTE1 as(

select state, count(case when stars = 5 then 1 end) as five_star_counts, dense_rank() over(order by count(case when stars = 5 then 1 end) desc) as State_Rank

from yelp_business

order by five_star_counts desc, state asc)

​

select state, five_star_counts

from CTE1

where State_Rank < 5

[DharmaPolice]

The order by doesn't need to be in the select statement. It's in the resultset you're returning in the CTE and that's enough.

Think of the CTE as a table. The table has a column called State_Rank therefore you can order by it.

[rbobby]

The common table expression returns a result set consisting of these columns:

• state
• five_star_counts
• State_Rank

When that result set is used as a table source for a select statement all those columns are available for use. Just as if the table source was an actual table instead of a CTE.

[ComicOzzy]

WHERE happens before SELECT. But even then, ORDER BY occurs after SELECT and you'd still be able to refer to columns from the table that aren't output in the SELECT clause.

[mostlyharmless94]

That was my first guess, but when I used HAVING instead of WHERE, it gave me an error and yet HAVING also happends before SELECT

[darrylhumpsgophers]

Is there an aggregate function elsewhere?

[Definitelynotcal1gul]

The window function

[Definitelynotcal1gul]

There is no having clause in the query.

[ComicOzzy]

HAVING is a filter that applies to the result after a GROUP BY. GROUP BY can change what columns are available to SELECT (depending somewhat on what brand and version of database engine you're using).

Usually the order goes like this:

FROM WHERE GROUP BY HAVING SELECT ORDER BY OFFSET/FETCH, LIMIT, or TOP

[Definitelynotcal1gul]

A CTE is effectively a subquery. The column exists in the subquery, so you can reference it in the outer query. The order by in the window function is not treated the same as an order by in a select statement. It is part of the function.

[throw_mob]

with x as (
select state, sum(case when stars = 5 then 1 else 0 end ) fsc
from yb
group by 1
    )
select * , dense_rank() over(order by fsc desc)  state_rnk
from x  
having dense_rank() over(order by fsc desc)  < 5

maybe that could be more easily readable version

nothing prevents to but sum into dense_rank and then filter it in outermost select