Please help debug! No results after adding 'WHERE' query into SQL query

I have a window function for the Climate Change exercise, and I am trying to understand how to give only a subset of a query.

The following code works fine:

SELECT 
RANK() OVER (
  PARTITION BY state
    ORDER BY tempc DESC
  ) AS warmest_rank,
  state, 
  year, 
  tempc
FROM state_climate;

And I get a result with a table of data. However, the following result does not:

SELECT 
RANK() OVER (
  PARTITION BY state
    ORDER BY tempc DESC
  ) AS warmest_rank,
  state, 
  year, 
  tempc
FROM state_climate
WHERE warmest_rank < 6;

And I have no idea why. My intention was to only show the ‘top 5’ ranks for each state, i.e. ones where the warmest_rank column only shows a 1/2/3/4/5. But instead, I get no table.

‘WHERE warmest_rank IS NOT NULL’ also fails; ‘WHERE tempc > 6’ works fine. So I imagine it’s something to do with the warmest_rank item.

Can anyone advise?

Because WHERE is processed before the SELECT statement–and you’re using an alias–so it doesn’t know what the alias is in reference to.

Google “order of execution” and you’ll find stuff like this.

2 Likes

I understand what you’re saying regarding order of execution, thank you!

So what is the correct step to get the result I am intending for? This doesn’t seem like a hugely complex use case, but I am not sure where I am going wrong.