title: Semi-Tricky SQL Query
body: <p>I am trying to write a query for SQL Server 2005 but I can't figure out how to do it.  I have a table with the following fields:</p>
  
  <p><strong>MessageID int<br/>
  CategoryID int<br/>
  Priority tinyint<br/>
  MessageText NVARCHAR(MAX)<br/></strong></p>
  
  <p>I need a query that will return * for each row that has the highest priority within a Category.  For example, if I had the following data:</p>
  
  <p><strong>MessageID, CategoryID, Priority, MessageText</strong><br/>
  1, 100, 1, Error #1234 occurred<br/>
  2, 100, 2, Error #243 occurred<br/>
  3, 100, 3, Error #976 occurred<br/>
  4, 200, 4, Error #194 occurred<br/>
  5, 200, 1, Error #736 occurred<br/>
  6, 300, 3, Error #54 occurred<br/>
  7, 300, 2, Error #888 occurred<br/></p>
  
  <p>then the result would be:</p>
  
  <p><strong>MessageID, CategoryID, Priority, MessageText</strong><br/>
  3, 100, 3, Error #976 occurred<br/>
  4, 200, 4, Error #194 occurred<br/>
  6, 300, 3, Error #54 occurred<br/></p>
  
  <p>Notice that it returns one row per category, and that it is the row which had the highest priority for that Category.</p>
  
  <p>Can anyone tell me how I can write this query?</p>