Aggregating data and the MAX function

enter image description here

I have a large table I am calling on and using a nested query for pulling the MAX CHANGEDATE out of a set of CHANGEDATE's within Policy parameters 01/01/2022 - 01/01/2023. I want to pull the most recent CHANGEDATE within the parameters, but since I am using the max function to aggregate everything it is also pulling the MAX value from WCPrem. How do I make it so I am only pulling the MAX CHANGE date while not getting an error for not using an aggregate function for the WCPrem columns?

I tried using MAX as an aggregate function when calling on the data from each column.

Solution

I suspect you want the latest changedate for each memberid, but you don't specify. If you want the latest changedate for each of something else, you'll need to specify what that something else is.

This would show one row for each memberid, where the changedate is the latest among them:

select    *
from      some_table as t
where     changedate =  (
                          select  max(changedate)
                          from    some_table as x 
                          where   x.memberid = t.memberid
                        );

If you want the latest changedate for each memberid, but only considering rows where the poleffdate is between 1/1/2022 and 1/1/2023, that would look like this:

select    *
from      some_table as t
where     changedate =  (
                          select  max(changedate)
                          from    some_table as x 
                          where   x.memberid = t.memberid
                              and x.poleffdate between #1/1/2022# and #1/1/2023#
                        );

However, I don't know if the poleffdate column is the column of interest, with regard to the date range you specified, because you talk about "Policy parameters" yet there is no "Policy parameters" column in the picture you attached. You'll have to say what column you want filtered for that timeframe, and how.