如何编写SQL子查询获取各分类最高奖金对应的code与description？

Got it, let's break this down! Your original query only pulls the highest prize entry across the entire table, but we need to adjust it to target each category's top prize instead. Let's cover two reliable approaches using subqueries (plus concrete examples to make it clear).

First, let's assume your table has a category column that defines the groups—just replace this with your actual category column name if it's different.

Approach 1: Subquery with JOIN (Works for Most SQL Databases) #

This method first calculates the maximum prize for each category, then joins that result back to the original table to fetch the matching code and description.

SELECT t.category, t.code, t.description, t.prize
FROM table_a t
JOIN (
    -- Subquery to get max prize per category
    SELECT category, MAX(prize) AS max_prize
    FROM table_a
    GROUP BY category
) AS category_max
ON t.category = category_max.category 
AND t.prize = category_max.max_prize;

Example Input (table_a) #

Example Output #

Approach 2: Window Functions (Modern SQL Databases) #

If your database supports window functions (like MySQL 8+, PostgreSQL, SQL Server, etc.), this is a cleaner option. We use ROW_NUMBER() (or RANK() for ties) to label each entry's rank within its category, then filter for the top-ranked entries.

For Single Top Entry per Category (even with ties) #

This will pick one entry if multiple entries share the highest prize (you can adjust the ORDER BY to set priority, e.g., by code):

SELECT category, code, description, prize
FROM (
    SELECT 
        category, 
        code, 
        description, 
        prize,
        -- Assign rank 1 to the highest prize in each category
        ROW_NUMBER() OVER (
            PARTITION BY category 
            ORDER BY prize DESC, code ASC
        ) AS prize_rank
    FROM table_a
) ranked_entries
WHERE prize_rank = 1;

For All Tied Top Entries per Category #

If you want to keep every entry that shares the highest prize in a category, replace ROW_NUMBER() with RANK():

SELECT category, code, description, prize
FROM (
    SELECT 
        category, 
        code, 
        description, 
        prize,
        RANK() OVER (
            PARTITION BY category 
            ORDER BY prize DESC
        ) AS prize_rank
    FROM table_a
) ranked_entries
WHERE prize_rank = 1;

For example, if we added an entry E003 | Tablet | 1000 to the Electronics category, the RANK() version would return both E001 and E003, while ROW_NUMBER() would only pick one (E001, since we added code ASC to the sort).

内容的提问来源于stack exchange，提问作者raspi surya