SQL Server查询：获取每个人员对应最高数量的记录

Hey there! I get that sifting through similar cases can be frustrating when you're trying to adapt them to your own scenario. Let's figure out how to retrieve only the record with the highest quantity for each person in your SQL Server testDB.

Assuming your three tables follow a typical structure (e.g., People with PersonID and Name, Products with ProductID and ProductName, ProductOrders linking people to products with a Quantity field), here are two solid approaches:

Approach 1: Get a Single Record Per Person (Even With Ties) #

Use ROW_NUMBER() to assign a unique rank to each order per person, sorted by quantity descending. This will pick one record if there's a tie in the highest quantity (you can add extra sorting to break ties if needed):

WITH RankedOrders AS (
    SELECT 
        p.Name,
        pr.ProductName,
        po.Quantity,
        -- Group orders by person, sort by quantity descending
        ROW_NUMBER() OVER (PARTITION BY p.PersonID ORDER BY po.Quantity DESC) AS OrderRank
    FROM ProductOrders po
    JOIN People p ON po.PersonID = p.PersonID
    JOIN Products pr ON po.ProductID = pr.ProductID
)
SELECT Name, ProductName, Quantity
FROM RankedOrders
WHERE OrderRank = 1;

Approach 2: Include All Tied Highest Quantity Records #

If you want to keep all orders where a person has multiple entries with the same maximum quantity, use RANK() instead of ROW_NUMBER():

WITH RankedOrders AS (
    SELECT 
        p.Name,
        pr.ProductName,
        po.Quantity,
        -- Assign same rank to orders with identical highest quantity
        RANK() OVER (PARTITION BY p.PersonID ORDER BY po.Quantity DESC) AS OrderRank
    FROM ProductOrders po
    JOIN People p ON po.PersonID = p.PersonID
    JOIN Products pr ON po.ProductID = pr.ProductID
)
SELECT Name, ProductName, Quantity
FROM RankedOrders
WHERE OrderRank = 1;

Quick Breakdown: #

• PARTITION BY p.PersonID: Groups all orders by individual person, so we process each person's orders separately.
• ORDER BY po.Quantity DESC: Ensures the highest quantity order gets the top rank in each group.
• ROW_NUMBER() vs RANK(): The former gives a unique number to every row (so only one top record), while the latter gives the same rank to rows with matching quantities (so all ties are included).

If your existing query already joins the tables, you can just drop that logic into the CTE and add the ranking function—no need to rewrite everything from scratch!

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