T-SQL: Selecting Column Based on MAX(Other Column)

Question

I'm hoping there's a simple way to do this without using a sub-query:

Scenario: You have "TableA" with columns "Key", "SubKey", and "Value". I need to get the "Value" of the MAX("SubKey") for a given "Key".

So if the Table contained the rows:

KEY SUBKEY VALUE
1   1      100
1   2      200
1   3      300

For Key = 1, I need the value 300. I was hoping to do something like this:

SELECT
  VALUE
FROM
  TableA
WHERE
  Key = 1
HAVING
  SubKey = MAX(SubKey)

But that's a no-go. Is there a way to do this without doing a 'WHERE SubKey = (subselect for max subkey)'?

Answer 1

OMG Ponies hit most of the ways to do it. Here's one more:

SELECT
    T1.value
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.key = T1.key AND
    T2.subkey > T1.subkey
WHERE
    T2.key IS NULL

The only time that T2.key will be NULL is when there is no match in the LEFT JOIN, which means that no row exists with a higher subkey. This will return multiple rows if there are multiple rows with the same (highest) subkey.