title: SQL ORDER BY Subquery
body: <p>My query is to obtain the top score of each player and the Time elapsed for that session.</p>
  
  <p>In MySQL the following Query is used and works</p>
  
  <pre><code>SELECT Playername as 'Player Name', HighScores.P1Score as 'Highest Score', TimeElapsed as 'Elapsed Time'
  FROM 
  (gamerecord inner join playerprofile ON playerprofile.PID = gamerecord.PID),
  (SELECT GID, PID, P1Score 
   FROM (SELECT GID, PID, P1Score 
         FROM gamerecord 
         ORDER BY 3 DESC) AS sortedtable 
   GROUP BY PID) AS HighScores 
  WHERE gamerecord.GID = Highscores.GID
  </code></pre>
  
  <p>Now, when working with a MSSQL Server, the following error is thrown.</p>
  
  <p>The <code>ORDER BY</code> clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless <code>TOP</code>, <code>OFFSET</code> or <code>FOR XML</code> is also specified.</p>
  
  <p>The innermost query is used to sort the result first before the GROUP BY operation. It is observed that <code>GROUP BY</code> takes the top most value and thus by sorting, I make sure the top value is the highest score.</p>
  
  <p>How may i make this work with MSSQL without that error being thrown?</p>