Semi-Tricky SQL Query

Question

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:

MessageID int
CategoryID int
Priority tinyint
MessageText NVARCHAR(MAX)

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:

MessageID, CategoryID, Priority, MessageText
1, 100, 1, Error #1234 occurred
2, 100, 2, Error #243 occurred
3, 100, 3, Error #976 occurred
4, 200, 4, Error #194 occurred
5, 200, 1, Error #736 occurred
6, 300, 3, Error #54 occurred
7, 300, 2, Error #888 occurred

then the result would be:

MessageID, CategoryID, Priority, MessageText
3, 100, 3, Error #976 occurred
4, 200, 4, Error #194 occurred
6, 300, 3, Error #54 occurred

Notice that it returns one row per category, and that it is the row which had the highest priority for that Category.

Can anyone tell me how I can write this query?

Answer 1

Verified:

SELECT
    highest_priority_messages.*
FROM
(
    SELECT
    m.MessageID
    , m.CategoryID
    , m.Priority
    , m.MessageText
    , Rank() OVER 
        (PARTITION BY m.CategoryID ORDER BY m.Priority DESC) AS p_rank
    FROM [Message] m
    GROUP BY 
        m.CategoryID 
        , m.Priority
        , m.MessageID
        , m.MessageText
) highest_priority_messages
WHERE 
    p_rank = 1