How to select the most up to date (max date) entry in SQL?-CodePudding

I have a database of responses from users. A user is allowed to go back in and change their response to any given question at anytime. What query can I use to best pull in only the most updated response by the user? I know it is something to do with a MAX() function on a date variable, but I get tripped on the details. Here is an example:

 USER_ID     SURVEY_NAME    QUESTION_NAME   UPDATE_DATE  RESPONSE
 A1           BIG SURVEY       Q1             01/01/22    BAD  
 A1           BIG SURVEY       Q2             01/01/22    GOOD  
 A1           BIG SURVEY       Q3             01/01/22    OK  
 A1           BIG SURVEY       Q1             01/08/22    GOOD

And from the data above, I just want to pull in this:

 USER_ID     SURVEY_NAME    QUESTION_NAME   UPDATE_DATE  RESPONSE
 A1           BIG SURVEY       Q2             01/01/22    GOOD  
 A1           BIG SURVEY       Q3             01/01/22    OK  
 A1           BIG SURVEY       Q1             01/08/22    GOOD

CodePudding user response：

You can use row_number() to find the rows with the latest dates for each (USER_ID,SURVEY_NAME,QUESTION_NAME) triplet:

with cte as
(select USER_ID,SURVEY_NAME,QUESTION_NAME,UPDATE_DATE,RESPONSE, 
row_number() 
over(partition by USER_ID,SURVEY_NAME,QUESTION_NAME
order by UPDATE_DATE desc) rn
from mytable)
select USER_ID,SURVEY_NAME,QUESTION_NAME,UPDATE_DATE,RESPONSE
from cte
where rn = 1;

Fiddle