title: SQL Return rows with highest value for a given field
body: <p>I was practising some SQL with this example data:</p>
  <p>employee_data</p>
  <pre><code>salary    first_name    department
  50000     Adam          Sales
  27000     Beth          HR
  70000     Dave          IT
  52000     Amy           Sales
  etc...
  </code></pre>
  <p>I wrote a query to return the records with the highest salary for each department. However, for some of the departments there were two or more people tied for the highest salary. So I wrote this short script:</p>
  <pre><code>WITH cte AS( 
  SELECT salary, first_name, department, ROW_NUMBER() OVER(PARTITION BY department) AS rownum
  FROM employee_data
  WHERE salary IN (SELECT MAX(salary) FROM employee_data GROUP BY department)
  )
  
  SELECT *
  FROM cte
  WHERE rownum = 1
  </code></pre>
  <p>This does return the correct output being only taking one record for each department where there is a tie for the highest salary. But I was wondering, is there a more efficient way to do it? With less lines or without the need to include a CTE.</p>