如何按类别选取绝对值最大的行并插入SQL结果表？

Alright, let's fix this properly. You need to select the full row with the largest absolute value of ExpValue for each CategoryID and insert it into #Result—your original query had an edge case flaw where it picked 0 instead of -59 for CategoryID 11. Let's break this down.

The Problem with the Original Query #

Your initial approach used ORDER BY ExpValue DESC, which works for positive values but fails when negative numbers have larger absolute values. For CategoryID 11, 0 is the "largest" value in a descending sort, but we actually need -59 (the number with the biggest absolute magnitude).

You also need to cover three specific scenarios:

• Categories with multiple positive values and 0: Return the largest positive value
• Categories with only 0s: Return any single 0 row
• Categories with multiple negative values and 0: Return the smallest (most negative) value (since it has the largest absolute value)

The Correct Query #

For a Single CategoryID #

If you're targeting one category at a time, adjust your sort to prioritize absolute value first:

INSERT INTO #Result 
SELECT TOP 1 * 
FROM #SmallTable 
WHERE CategoryID = 11 
ORDER BY ABS(ExpValue) DESC, ExpValue DESC;

The ABS(ExpValue) DESC ensures the row with the biggest magnitude comes first. The optional ExpValue DESC tiebreaker picks positive values over negatives if there's a tie (e.g., 59 and -59). If you'd prefer the negative in ties, swap it to ExpValue ASC.

For All Categories at Once (Batch Processing) #

If you need to handle every category in one go (much more efficient for large datasets), use a window function like ROW_NUMBER() to rank rows per category:

INSERT INTO #Result
SELECT t.*
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY CategoryID 
               ORDER BY ABS(ExpValue) DESC, ExpValue DESC
           ) AS rn
    FROM #SmallTable
) t
WHERE t.rn = 1;

This partitions your data by CategoryID, ranks each row within the partition by absolute value (and tiebreaker), then selects only the top-ranked row for each category.

Test Case Validation #

Let's confirm with your sample data:

CREATE TABLE mytable ( CategoryID INTEGER NOT NULL, ExpValue INTEGER NOT NULL );
INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, 0);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, -13);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (11, -59);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, 0);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, 59);
INSERT INTO mytable (CategoryID, ExpValue) VALUES (130, 2);

Running the batch query will populate #Result with your expected output:

+------------+----------+
| CategoryID | ExpValue |
+------------+----------+
| 11         | -59      |
| 130        | 59       |
+------------+----------+

Important Note #

Since your table has 30 columns, using SELECT * will preserve all column data exactly as needed—no need to list every column manually.

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