title: Classifying recordsets based on counts
body: <p>I have students that are either non-matriculated or matriculated in another school but are enrolled in one or more or our classes in one or more subjects.  The view v_Enrollments returns a complete list of students (NetId) and the subject (Subj) of the class they are taking.  </p>
  
  <p>For accounting reasons, I need to assign each student to one and only one department (based on class subject). If they are taking multiple classes across different subjects, priority is based on subject count.  If they are taking an equal number of classes in different subjects, then the tie is broken (pseudo)randomly.</p>
  
  <p>I am not fluent with windowing functions and struggled a bit to come up with a solution. The below query works but I can't help but wonder if there is a better solution. </p>
  
  <pre><code>select NetId, Subj
  from (
      select NetId, Subj, RowNum
          , Max(RowNum) Over (partition by NetId) as MaxRowNum
      from (
          select NetId, Subj
              , Count(*) as Cnt
              , MAX(Count(*)) Over(partition by NetId) as MaxCnt
              , ROW_NUMBER() Over (partition by NetId order by checksum(NetId, Subj)) as RowNum
                                                      -- psuedorandom but repeatable ordering
          from v_Enrollment
          group by NetId, Subj
      ) as s2
      where Cnt = MaxCnt
  ) as s1
  where RowNum = MaxRowNum
  order by NetId
  </code></pre>