title: Select all where max dense_rank() = value?
body: <p>I have code that uses dense_rank, partitioned by an ID and ordered by val2.
  I want to select all the rows where the max rank for any given ID is 2.</p>
  
  <p>The below code provides the correct output for initial steps.</p>
  
  <pre><code>Select distinct 
  `account id`, `val2`, distinct_rank, observation_total
  from ( Select 
  `account id`, `val2`, dense_rank() 
  over (partition by `account id` order by `val2` desc) as distinct_rank,
  count(*) 
  over (partition by `account id` order by `val2` desc) as observation_total
  from TABLE1 );
  </code></pre>
  
  <p>Sample Result:</p>
  
  <pre><code>account id | val2 | distinct_rank 
  ___________________________________
  1          | a    | 1
  2          | a    | 1
  2          | b    | 2
  2          | c    | 3
  3          | d    | 1
  3          | e    | 2
  </code></pre>
  
  <p>So I need a query that will only select rows where the max distinct_rank per account id = 2, or some other value.</p>
  
  <p>So for example, if max distinct_rank per account id = 2, result would be:</p>
  
  <pre><code>account id | val2 | distinct_rank 
  ___________________________________
  3          | d    | 1
  3          | e    | 2
  
  This is because account id = 3 has a max distinct_rank of 2.
  </code></pre>
  
  <p>I know how to select where max rank = 2, and I could just do a query that says "select * where account id in a subquery that grabs all with max distinct_rank = 2" but I feel like there might be a better / more efficient way?</p>