How Can I Efficiently Retrieve the Maximum Value and Associated Value in SQL with Grouping?

An elegant solution to efficiently obtain the maximum value and its associated values ​​in SQL grouping

In SQL, it is often tricky to find the maximum value in another column and get its associated value based on the third column after grouping. Consider the following scenario:

The following forms are available:

KEY	NUM	VAL
A	1	AB
B	1	CD
B	2	EF
C	2	GH
C	3	HI
D	1	JK
D	3	LM

The goal is to find the VAL value corresponding to the maximum NUM for each KEY. The expected results are as follows:

KEY	VAL
A	AB
B	EF
C	HI
D	LM

Though the following query can be used to achieve the goal:

<code class="language-sql">select KEY, VAL
from TABLE_NAME TN
where NUM = (
    select max(NUM)
    from TABLE_NAME TMP
    where TMP.KEY = TN.KEY
    )</code>

But when there are many KEY values, this method seems clumsy. However, using the row_number() function provides a more elegant solution:

<code class="language-sql">select key, val
from (select t.*, row_number() over (partition by key order by num desc) as seqnum
      from table_name t
     ) t
where seqnum = 1;</code>

This method utilizes the concepts of partitioning and sorting to obtain the desired results. By setting row_number()'s partition to KEY and sorting by NUM in descending order, each row in each partition is assigned a sequence number. Then select the row with serial number 1 to obtain the maximum NUM value corresponding to each KEY and its associated VAL value.

It is important to note that this improved approach behaves slightly differently than the original query. The original query may return multiple rows for each KEY, while this solution ensures that only one row per KEY is returned. If you need to retain the original behavior, you can replace dense_rank() with rank() or row_number().

The above is the detailed content of How Can I Efficiently Retrieve the Maximum Value and Associated Value in SQL with Grouping?. For more information, please follow other related articles on the PHP Chinese website!