title: Find Max date without subquery
body: <p>I have simple database. What I want.</p>
  
  <p>Get all employees who</p>
  
  <ol>
  <li>over 50 years </li>
  <li>participate in the development of 10 products
  and more</li>
  </ol>
  
  <p>Also</p>
  
  <p>The result should contain</p>
  
  <ol>
  <li>Age of the employee </li>
  <li>the employee's name </li>
  <li>the last name of the product (for ProductEmployee.Date date)</li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/rBolN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rBolN.png" alt="enter image description here"></a></p>
  
  <p>I wrote this simple query:</p>
  
  <pre><code>SELECT e.Name, e.Age, p.Name
      FROM ProductEmployee pe1
      JOIN Employee e
          ON e.ID = pe1.EmployeeID
      JOIN ProductEmployee pe2
          ON pe2.EmployeeID = e.ID
      JOIN (
          SELECT pe3.EmployeeID, Max(pe3.Date) Date 
              FROM ProductEmployee pe3
              GROUP BY pe3.EmployeeID
      )y ON(y.Date = pe1.Date)    
      JOIN Product p
          ON p.ID = pe1.ProductID
      WHERE e.Age &gt; 50
      GROUP BY pe1.EmployeeID, e.Name, e.Age, p.Name
  HAVING Count(DISTINCT pe2.ProductID) &gt; 10
  ORDER BY e.Name
  </code></pre>
  
  <p>But I have a question. Can this write without a subquery? In one sentence.</p>