title: Using Rank() in SQL to rank groups
body: <p>Here is an SQL query (in the Google BigQuery environment using one of their demo datasets, but this is a standard SQL question):</p>
  
  <pre><code>SELECT name, sum(number) as namecount, RANK(name) 
  OVER (ORDER BY decade, namecount DESC) as PLACEMENT,
  
  case 
  when year &gt;= 1940 and year &lt;= 1949 then '40s' 
  when year &gt;= 1950 and year &lt;= 1959 then '50s'
  when year &gt;= 1960 and year &lt;= 1969 then '60s'
  when year &gt;= 1970 and year &lt;= 1979 then '70s'
  when year &gt;= 1980 and year &lt;= 1989 then '80s'
  when year &gt;= 1990 and year &lt;= 1999 then '90s'
  when year &gt;= 2000 and year &lt;= 2014 then 'Nowish'
  else
      'other'
  end   as  DECADE
  
  FROM [bigquery-public-data:usa_names.usa_1910_2013]  
  where gender = 'F' and year &gt; 1939   
  group by decade, name
  having namecount &gt; 25000
  order by decade, namecount DESC 
  
  limit 1000000 
  </code></pre>
  
  <p>Let's say I am interested in knowing the rank for <strong>Linda</strong> for each decade.  Thus, I would like the query to tell me Linda is ranked 2nd in the 1940s and Linda is ranked 2nd in the 1950s.  However the rank order is absolute in the given query, thus Linda is 118th in 1950s (Mary is the leader in the 1950s at 117th). </p>
  
  <p>A snippet of the result set:</p>
  
  <pre><code>name    namecount Placement Decade
  
  Mary    639971      1       40s  
  Linda   531587      2       40s 
  [ ... ]
  Mary    625464    117       50s
  Linda   564204    118       50s
  </code></pre>
  
  <p>How to reset the rank so that Mary shows up as #1 for the 1950s and Linda shows up as #2, and to reset the ranks in a similar manner for each subsequent decade?</p>