title: Finding most popular and most unique records using SQL
body: <p>My mom wanted a baby name game for my brother's baby shower. Wanting to learn python, I volunteered to do it. I pretty much have the python bit, it's the SQL that is throwing me.</p>
  
  <p>The way the game is supposed to work is everyone at the shower writes down names on paper, I manually enter them into Excel (normalizing spellings as much as possible) and export to MS Access. Then I run my python program to find the player with the most popular names and the player with the most unique names. The database, called "babynames", is just four columns.</p>
  
  <pre><code>ID | BabyFirstName | BabyMiddleName | PlayerName
  ---|---------------|----------------|-----------
  </code></pre>
  
  <p>My mom has changed things every so often, but as they stand right now, I have to figure out :</p>
  
  <p>a) The most popular name (or names if there is a tie) out of all first and middle names</p>
  
  <p>b) The most unique name (or names if there is a tie) out of all the first and middle names</p>
  
  <p>c) The player that has the most number of popular names (wins a prize)</p>
  
  <p>d) The player that has the most number of unique names (wins a prize)</p>
  
  <p>I've been working on this for about a week now and can't even get a SQL query for a) and b) to work, much less c) and d). I'm more than just a bit frustrated.</p>
  
  <p>BTW, I'm just looking at spellings of the names, not phonetics. As I manually enter names, I will change names like "Kris" to "Chris" and "Xtina" to "Christina" etc.</p>
  
  <p>Editing to add a couple of the most recent queries I tried for a)</p>
  
  <pre><code>SELECT [BabyFirstName], 
         COUNT ([BabyFirstName]) AS 'FirstNameOccurrence' 
  FROM [babynames] 
  GROUP BY [BabyFirstName] 
  ORDER BY 'FirstNameOccurrence' DESC 
  LIMIT 1
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT [BabyFirstName] 
  FROM [babynames] 
  GROUP BY [BabyFirstName] 
  HAVING COUNT(*) = 
      (SELECT COUNT(*) 
       FROM [babynames] 
       GROUP BY [BabyFirstName] 
       ORDER BY COUNT(*) DESC 
       LIMIT 1)
  </code></pre>
  
  <p>These both lead to syntax errors.</p>
  
  <pre><code>pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in ORDER BY clause. (-3508) (SQLExecDirectW)')
  </code></pre>
  
  <p>I've tried using [FirstNameOccurrence] and just FirstNameOccurrence as well with the same error. Not sure why it's not recognizing it by that column name to order by.</p>
  
  <pre><code>pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression 'COUNT(*) = (SELECT COUNT(*) FROM [babynames] GROUP BY [BabyFirstName] ORDER BY COUNT(*) DESC LIMIT 1)'. (-3100) (SQLExecDirectW)")
  </code></pre>
  
  <p>I'll admit that I'm not really grokking all of the COUNT(*) commands here, but this was a solution for a similar issue here in stackoverflow that I figured I'd try when my other idea didn't pan out.</p>