title: SQL: Find MIN but greater than a MAX
body: <p>I am working with task history and trying to find two dates attached to the same record:  1) Most recent time a task was approved (max approve); 2)The first submitted date after said approval.  </p>
  
  <p>Here is what I have so far: </p>
  
  <pre><code>Select  
  a.assn_uid,
  max(b.ASSN_TRANS_DATE_ENTERED) as LastApprove, 
  e.LastSubmitted 
  
  FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] a
  inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] b
  on a.ASSN_TRANS_UID = b.ASSN_TRANS_UID
  
  
  
  join (select c.assn_uid,
  min(d.ASSN_TRANS_DATE_ENTERED) as LastSubmitted
  
  FROM [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS] c
  inner join [PRJDEV_ProjectWebApp].[pub].[MSP_ASSIGNMENT_TRANSACTIONS_COMMENTS] d
  on c.ASSN_TRANS_UID = d.ASSN_TRANS_UID
  
  where c.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
  and d.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 0
  
  group by c.assn_uid ) e
  on e.ASSN_UID = a.ASSN_UID
  
  
  where a.ASSN_UID = '499879BC-28B2-E411-8B0A-00059A3C7A00'
  and b.[ASSN_TRANS_COMMENT_TYPE_ENUM] = 1
  
  group by a.assn_uid, e.LastSubmitted
  </code></pre>
  
  <p>This is close, however, it gives me the first time ever that the task was submitted.  I am sure that I need to use another subquery, I just dont know how to reference a column within the same result.  </p>
  
  <p>Here is the task history.  Highlighted are the two dates I am trying to show: 
  <img src="https://i.stack.imgur.com/ZQz16.jpg" alt="enter image description here"></p>