How to Fetch the Maximal VAL for Each KEY in Grouped Data Using Oracle SQL?

Extracting the Highest VAL for Each KEY in Oracle SQL

This guide demonstrates how to efficiently retrieve the maximum VAL for each KEY in grouped data using Oracle SQL. Let's examine a common scenario:

A table named TABLE_NAME contains the following data:

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 generate a result set showing only the maximum VAL for each unique KEY:

KEY	VAL
A	AB
B	EF
C	HI
D	LM

Efficient Oracle SQL Solution (10g and later):

While subqueries can achieve this, a more elegant and often faster solution uses the ROW_NUMBER() analytic function:

<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 query first assigns a rank (seqnum) to each row within each KEY partition, ordered by NUM in descending order. Then, it filters the results to only include rows with seqnum = 1, effectively selecting the row with the highest NUM (and thus, implicitly, the highest VAL assuming a consistent relationship between NUM and VAL within each key).

Important Note: This method guarantees one row per KEY. If you need to handle potential ties in NUM differently (e.g., return multiple rows for a KEY if multiple rows share the maximum NUM), consider using RANK() or DENSE_RANK() instead of ROW_NUMBER().

The above is the detailed content of How to Fetch the Maximal VAL for Each KEY in Grouped Data Using Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!