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

Answer 2

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

Answer 3

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.

Answer 4

If you'll always want just one row for one key value rather than the answer for many keys at once, all the join stuff is useless overbuilding. Just use the TOP 1 query OMG Ponies already gave you.