SQL Server技术求助：如何从重复日期中获取最大值？

Hey there! Let's tackle this common problem where you need to pull the maximum value corresponding to each duplicate date in your SQL Server table. I'll walk you through a couple of reliable solutions based on what exactly you need from your results.

First, Let's Set Up Example Data #

Let's assume your table looks something like this (adjust column names to match your actual schema):

CREATE TABLE your_table (
    date_column DATE,
    value_column INT,
    other_column VARCHAR(50) -- Optional: other columns you might need to keep
);

INSERT INTO your_table VALUES
('2024-01-01', 10, 'Record A'),
('2024-01-01', 20, 'Record B'),
('2024-01-02', 15, 'Record C'),
('2024-01-02', 25, 'Record D'),
('2024-01-03', 30, 'Record E');

Solution 1: Simple Group By + MAX() (For Date + Max Value Only) #

If you only need the date and its corresponding maximum value (no other columns), this is the most straightforward approach:

SELECT 
    date_column,
    MAX(value_column) AS max_value
FROM your_table
GROUP BY date_column
ORDER BY date_column;

This query groups all rows by date_column, then uses the MAX() aggregate function to grab the highest value_column for each group.

Solution 2: Window Functions (To Keep Full Rows) #

If you need to retain other columns from the row that has the maximum value (like other_column in our example), window functions are the way to go. We'll use ROW_NUMBER() to rank rows within each date group:

WITH ranked_records AS (
    SELECT 
        *,
        -- Rank rows in each date group by value descending (highest first)
        ROW_NUMBER() OVER (PARTITION BY date_column ORDER BY value_column DESC) AS row_rank
    FROM your_table
)
SELECT 
    date_column,
    value_column AS max_value,
    other_column
FROM ranked_records
WHERE row_rank = 1;

• PARTITION BY date_column splits the data into groups based on each unique date.
• ORDER BY value_column DESC ensures the row with the highest value gets a rank of 1.
• We filter for row_rank = 1 to get only the top row per date.

Note: Handling Tied Max Values #

If multiple rows have the same maximum value for a date and you want to keep all of them, replace ROW_NUMBER() with RANK() or DENSE_RANK():

WITH ranked_records AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY date_column ORDER BY value_column DESC) AS row_rank
    FROM your_table
)
SELECT 
    date_column,
    value_column AS max_value,
    other_column
FROM ranked_records
WHERE row_rank = 1;

This will return all rows that share the maximum value for a given date, instead of picking just one randomly.

Pick the solution that best fits your use case, and adjust the column names to match your actual table structure!

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