Max function is not filtering results

Question

I am trying to only get the most recent start date based on the last updated date field. When I try to use max, it is not filtering the results to only the most recent data.

select max(a.LAST_UPDATED_DATE),a.term_start  
from table AS a
where a.ID='123456' and location='Colorado' 
group by 2

This is the output I am getting

MAX(a.LAST_UPDATED_DATE)	TERM_START
2023-02-27T18:27:50Z	2023-04-12T07:00:00Z
2023-01-24T18:26:44Z	2023-04-05T07:00:00Z

I only want to get

MAX(a.LAST_UPDATED_DATE)	TERM_START
2023-02-27T18:27:50Z	2023-04-12T07:00:00Z

Answer 1

I've tried this on Oracle, SQL Server, and HANA:

    Select
    *
    from
    (
    select 
     ROW_NUMBER() OVER( PARTITION BY a.ID, a.location
         ORDER BY term_start DESC, a.LAST_UPDATED_DATE DESC
     ) row_num,
     a.LAST_UPDATED_DATE,
     a.term_start  
    from  a
    where a.ID='123456' and 
    location='Colorado'
    ) as test
    where row_num = 1

It will still work if you remove `where a.ID='123456' and location='Colorado' and you will see all of the locations and ids in your result

Using each a.ID and location combination in the partition where row_num = 1 returns the most recent value since within the partition the values are ordered term_start DESC, a.LAST_UPDATED_DATED_DATE DESC.

In your code, you are grouping on the term_start which results in unique values for TERM_START, therefore, two rows. You might want to group on the a.ID and location instead. You will get the MAX (a.LAST_UPDATED) per location. In the intent of the query you also mentioned you wanted most recent. The MAX and Most recent are not necessarily the same.