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

Using a self join:

This will return all the values with subkey values that match, in case there are multiples.

SELECT a.value  FROM TABLE a  JOIN (SELECT MAX(t.subkey) AS max_subkey          FROM TABLE t         WHERE t.key = 1) b ON b.max_subkey = a.subkey WHERE a.key = 1

Using RANK & CTE (SQL Server 2005+):

This will return all the values with subkey values that match, in case there are multiples.

WITH summary AS (  SELECT t.*,         RANK() OVER(ORDER BY t.subkey DESC) AS rank    FROM TABLE t   WHERE t.key = 1)SELECT s.value  FROM summary s WHERE s.rank = 1

Using ROW_NUMBER & CTE (SQL Server 2005+):

This will return one row, even if there are more than one with the same subkey value...

WITH summary AS (  SELECT t.*,         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank    FROM TABLE t   WHERE t.key = 1)SELECT s.value  FROM summary s WHERE s.rank = 1

Using TOP:

This will return one row, even if there are more than one with the same subkey value...

  SELECT TOP 1         t.value    FROM TABLE t   WHERE t.key = 1ORDER BY t.subkey DESC

Very simple, no join, no sub-query:

SELECT FIRST_VALUE(Value) OVER (ORDER BY SubKey DESC)FROM TableAWHERE Key = 1

If you need max value for each Key:

SELECT DISTINCT Key, FIRST_VALUE(Value) OVER (PARTITION BY Key ORDER BY SubKey DESC)FROM TableA

SELECT MAX(Value)FROM TableA t1GROUP BY Key, SubKeyHAVING SubKey = (SELECT MAX(SubKey) FROM TableA t2 WHERE t1.Key = t2.Key)  AND Key = 1