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

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 = 1 ORDER BY t.subkey DESC