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?