title: RANK(), DENSE_RANK(), and COUNT() in SQL Server
body: <p>I have a table of students who use programs/PGMs at the College.  I want to know, in a given month, for the top 100 PGMs: </p>
  
  <ol>
  <li>how many times the program/PGM was run (in a month), </li>
  <li>the ranking of the PGM, </li>
  <li>how many total ... unique SIDs ... Student ID ran the PGM, and </li>
  <li>the ranking of the count of unique students</li>
  </ol>
  
  <p>I was given this MS SQL 2010 code below ... to find the top 100 PGMs/programs.  I've really cleaned up my hacking around of nested SELECTs, COUNT, COUNT DISTINCT, RANK, etc ... Trust me, I've tried, copying SQL from other problems and answers.  I was hoping not to have tp make a temporary table.  Everything I tried was 'no joy' :(
  So i thought I would email the StackOverflow community.  Any help would be greatly appreciated and thank you in advance.</p>
  
  <pre><code>SELECT TOP 100
      T.PGM AS 'Program'
      ,COUNT (*) AS 'Program Count'
  
  FROM StudentPrograms T
  
  WHERE 
      T.PGM &lt;&gt;'unknown'
      AND T.CreateDate &gt; '10/01/2015'
      AND T.CreateDate &lt; '10/31/2015'
  
   GROUP BY       
     T.PGM
  
  ORDER BY 
      COUNT(*) DESC
      ;
  </code></pre>
  
  <p>I should end up with 5 columns in a table</p>
  
  <ol>
  <li>Program</li>
  <li>Program Count</li>
  <li>Program Rank (will be 1 through 100 ... boring I know ... but that's OK for now)</li>
  <li>Student Count</li>
  <li>Student Rank (won't be straight 1 to 100 but scattered around a bit)</li>
  </ol>