title: Impala Analytical functions in where clause
body: <p>So the basic premise of the question is that I have some huge tables in hadoop that I need to get some samples from each month. I've mocked up the below to show the sort of thing I'm after but obviously it's not real data...</p>
  
  <pre><code>--Create the table
  CREATE TABLE exp_dqss_team.testranking (
    Name STRING,
    Age INT,
    Favourite_Cheese STRING
    ) STORED AS PARQUET;
  
  --Put some data in
  INSERT INTO TABLE exp_dqss_team.testranking
  VALUES (
    ('Tim', 33, 'Cheddar'),
    ('Martin', 49, 'Gorgonzola'),
    ('Will', 39, 'Brie'),
    ('Bob', 63, 'Cheddar'),
    ('Bill', 35, 'Brie'),
    ('Ben', 42, 'Gorgonzola'),
    ('Duncan', 55, 'Brie'),
    ('Dudley', 28, 'Cheddar'),
    ('Edmund', 27, 'Brie'),
    ('Baldrick', 29, 'Gorgonzola'));
  </code></pre>
  
  <p>What I want to get is something like the youngest 2 people in each cheese category. The below gives me the age rankings for each cheese category but won't restrict it to the top two:</p>
  
  <pre><code>SELECT RANK() OVER(PARTITION BY favourite_cheese ORDER BY age asc) AS rank_my_cheese, favourite_cheese, name, age
  FROM exp_dqss_team.testranking;
  </code></pre>
  
  <p>If I add a <code>WHERE</code> clause it gives me the following error: </p>
  
  <blockquote>
    <p><em>WHERE clause must not contain analytic expressions</em></p>
  </blockquote>
  
  <pre><code>SELECT RANK() OVER(PARTITION BY favourite_cheese ORDER BY age asc) AS rank_my_cheese, favourite_cheese, name, age
  FROM exp_dqss_team.testranking
  WHERE RANK() OVER(PARTITION BY favourite_cheese ORDER BY age asc) &lt;3;
  </code></pre>
  
  <p>Is there a better way to do this than creating a table of all the rankings and then selecting from that with a <code>WHERE</code> clause on the ranking?</p>