含CASE语句的HAVING子句查询问题：max(col4_date)使用错误求助

Your current approach runs into trouble because you can't directly compare an aggregate value like MAX(col4_date) to individual row values in the HAVING clause—aggregate functions operate on entire groups, not single rows. Let's break down two reliable ways to fix this:

Solution 1: Subquery + Join to Match Group Max Dates #

First, we'll find the latest date for each (col1, col2, col3) group, then join that back to the original table to get the row(s) associated with that date, and filter for col5='Hi':

SELECT t1.col1, t1.col2, t1.col3
FROM table1 t1
INNER JOIN (
    -- Get the maximum date per group
    SELECT col1, col2, col3, MAX(col4_date) AS max_group_date
    FROM table1
    GROUP BY col1, col2, col3
) group_max_dates 
    ON t1.col1 = group_max_dates.col1
    AND t1.col2 = group_max_dates.col2
    AND t1.col3 = group_max_dates.col3
    AND t1.col4_date = group_max_dates.max_group_date
WHERE t1.col5 = 'Hi'
GROUP BY t1.col1, t1.col2, t1.col3;

This works because the subquery isolates the latest date for each group, then we only keep rows from the original table that match that date and have col5='Hi'. The final GROUP BY ensures we get unique groups.

Solution 2: Window Functions to Rank Rows by Date #

Using window functions like RANK() lets us directly mark the most recent row(s) in each group, then filter for those rows where col5='Hi':

WITH ranked_group_rows AS (
    SELECT
        col1, col2, col3, col5,
        -- Assign rank 1 to rows with the latest date in each group
        RANK() OVER (
            PARTITION BY col1, col2, col3
            ORDER BY col4_date DESC
        ) AS date_rank
    FROM table1
)
SELECT DISTINCT col1, col2, col3
FROM ranked_group_rows
WHERE date_rank = 1 AND col5 = 'Hi';

• PARTITION BY splits the data into groups based on your three columns.
• ORDER BY col4_date DESC makes the latest date in each group get rank 1.
• RANK() instead of ROW_NUMBER() handles ties (if multiple rows share the same latest date in a group)—if any of those top-ranked rows has col5='Hi', the group is included.

Why Your Original Query Failed #

In your HAVING clause, MAX(col4_date) is an aggregate value for the entire group, but col4_date inside the CASE refers to individual row values. SQL doesn't allow mixing aggregated and non-aggregated columns in this context unless the non-aggregated column is part of the GROUP BY (which col4_date isn't here). That's why your comparison MAX(col4_date) = col4_date doesn't behave as expected.

Example Table Structure #

col1 | col2 | col3 | col4_date | col5
-----+------+------+-----------+-----

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