SQL Server：当行的A、B、C列值重复时选取D列最大值所在行

Hey there! Let's work through this problem where you need to retain only the row with the largest D value for every unique combination of columns A, B, and C. Below are practical solutions for common tools you might be using:

SQL (Database Query) #

This is the standard approach for relational databases. We use a window function to rank rows within each group, then filter for the top-ranked row:

WITH ranked_records AS (
    SELECT 
        *,
        -- Rank rows in each A/B/C group by D descending
        ROW_NUMBER() OVER (PARTITION BY A, B, C ORDER BY D DESC) AS rank_num
    FROM your_table_name
)
-- Select only the top-ranked row (max D) from each group
SELECT ID, A, B, C, D, E
FROM ranked_records
WHERE rank_num = 1;

Note: #

• If multiple rows in a group have the same maximum D value, ROW_NUMBER() will pick just one (arbitrarily). To keep all rows with the max D, replace ROW_NUMBER() with RANK() or DENSE_RANK().

Python (Pandas) #

For data analysis workflows using Pandas, this is a concise way to get your desired result:

import pandas as pd

# Load your data into a DataFrame (adjust as needed)
# df = pd.read_csv("your_data.csv")

# Get the index of the row with max D for each A/B/C group
max_d_indices = df.groupby(['A', 'B', 'C'])['D'].idxmax()

# Filter the DataFrame to keep only those rows
result_df = df.loc[max_d_indices].reset_index(drop=True)

# Print or export the result
print(result_df)

Note: #

• idxmax() returns the first occurrence of the maximum D value in each group. To keep all rows with the max D, use this instead:

  result_df = df[df['D'] == df.groupby(['A', 'B', 'C'])['D'].transform('max')]
  

Excel (Spreadsheet) #

For users working in Excel, here are two methods:

Method 1: Helper Column & Filter #

1. Add a helper column (e.g., column F) and enter this formula in cell F2, then drag down:

   =RANK.EQ(D2, $D$2:$D$5, 0)+COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2)-1
   

2. Filter column F to show only rows with a value of 1 — these are your desired rows.

Method 2: Dynamic Array Formula (Excel 365/2021) #

Use this single formula to directly output the filtered result (adjust the range A2:E5 to match your data):

=LET(data,A2:E5,cols,COLUMNS(data),group_cols,{2,3,4},val_col,5,max_vals,BYROW(data,LAMBDA(r,MAX(FILTER(INDEX(data,,val_col),MMULT(--(INDEX(data,,group_cols)=INDEX(r,,group_cols)),SEQUENCE(COLUMNS(group_cols),,1,0))=1)))),FILTER(data,INDEX(data,,val_col)=max_vals))

Expected Result #

Here's the final output matching your requirement:

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