55

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)'?

Tom H
  • 46,766
  • 14
  • 87
  • 128
John
  • 17,163
  • 16
  • 65
  • 83

8 Answers8

73

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Since the actual query is several tables linked together, I ended up going with the 'select top 1, ordered desc' method, since that seemed to be the easiest to read – John Sep 09 '10 at 20:37
  • @John: That's fine, but if you need to deal with the highest value per key then the analytic versions (RANK, ROW_NUMBER) are more accommodating. – OMG Ponies Sep 09 '10 at 20:44
  • If you are using SQL Server 2012 or newer, see the answer below from @Nguyen. https://stackoverflow.com/a/35477271/269123 – Abraham May 29 '20 at 02:18
47

Very simple, no join, no sub-query:

SELECT FIRST_VALUE(Value) OVER (ORDER BY SubKey DESC)
FROM TableA
WHERE 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
Nguyen Viet Cuong
  • 606
  • 5
  • 6
  • For the second example, you can get multiple columns by using a named window. https://dev.mysql.com/doc/refman/8.0/en/window-functions-named-windows.html – Joey Carlisle Aug 15 '23 at 22:20
4
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
Amy B
  • 108,202
  • 21
  • 135
  • 185
4

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.

Tom H
  • 46,766
  • 14
  • 87
  • 128
1

OMG Ponie's ROW_NUMBER method is the one that will work best in all scenarios as it will not fail in the event of having two MAX values with the same amount returning more records than expected and breaking a possible insert you might have being fed by that recordset.

One thing that is missing is how to do it in the event of having to return the subkey associated to each max value, when there are also multiple keys. Simply join your summary table with a MIN and GROUP "itself" and off you go. 

WITH summary AS (
  SELECT t.*,
         ROW_NUMBER() OVER(ORDER BY t.subkey DESC) AS rank
    FROM TABLE t
   WHERE t.key = 1)
SELECT s.*
  FROM summary s
  join  (select key, min(rank) as rank
        from summary
        group by key) sMAX
        on s.key = sMAX.key and r.rank = sMAX.rank
Community
  • 1
  • 1
Diego
  • 33
  • 6
0

In case of multiple keys using a CTE:

WITH CTE AS
(
    SELECT key1, key2, MAX(subkey) AS MaxSubkey
    FROM TableA 
    GROUP BY key1, key2
)
SELECT a.Key1, a.Key2, a.Value
FROM TableA a
    INNER JOIN CTE ON a.key1 = CTE.key1 AND a.key2 = CTE.key2 AND
                      a.subkey = CTE.MaxSubkey
Glen
  • 802
  • 1
  • 11
  • 27
0

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.

ErikE
  • 48,881
  • 23
  • 151
  • 196
0

May be faster then "first_value" :

select distinct on ("Key") "Key", "Value"
from tablea
order by "Key", "SubKey" desc
Alexey Ki
  • 41
  • 1
  • 5