title: T-SQL query to fetch row with multiple aggregate functions
body: <p>I have the following problem. I need to fetch the row for each distinct user_id with the min rank, max end date, max begin date, and max sequence number <em>in that order</em>. Essentially, I want a user's highest ranked, most recent <code>MAJOR_ID</code>. I am trying to avoid making separate temp tables and joining on those aggregate functions like the following:</p>
  
  <pre><code>select USER_ID
      , SEQ_NBR
      , BEGIN_DATE
      , END_DATE
      , MAJOR_RANK
      , MAJOR_ID
      , DEGREE_CODE
  into #major0
  from majors
  
  select USER_ID
      , MIN(MAJOR_RANK) as MAJOR_RANK
  into #major1
  from #major0
  group by USER_ID
  
  select #major0.USER_ID
      , #major0.MAJOR_RANK
      , MAX(#major0.END_DATE) as END_DATE
  into #major2
  from #major0
  inner join #major1 on #major0.USER_ID = #major1.USER_ID and #major0.MAJOR_RANK = #major1.MAJOR_RANK
  group by #major0.USER_ID
      , #major0.MAJOR_RANK
  </code></pre>
  
  <p>etc...</p>
  
  <p>until I get to that row that satisfies all the criteria, and I join back on all the fields from the original query. Does that make sense? It's a lot of work to write this out, and I can't create a view of it unless I made a absurdly long set of subqueries, I don't think I can utilize <code>MIN(MAJOR_RANK) OVER (PARTITION BY USER_ID)</code> in a subquery for all these fields because I will lose records that don't satisfy all of them.</p>
  
  <p>Any suggestions would help! Thank you!</p>