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

Question

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

Maybe this helps: https://stackoverflow.com/search?q=sql+top+per+group — Thorsten Kettner, Mar 16 '22 at 17:24
Does this answer your question? [How to SELECT by MAX(date)?](https://stackoverflow.com/questions/7836036/how-to-select-by-maxdate) — Mushroomator, Mar 16 '22 at 17:24
Then, when asking SQL questions, please always tag the request with the DBMS you are using. The answer may heavily depend on what your DBMS features. — Thorsten Kettner, Mar 16 '22 at 17:25
@Mushroomator: The request you linked to has a lot of incorrect answers and no accepted answer. — Thorsten Kettner, Mar 16 '22 at 17:33

score 1 · Accepted Answer · answered Mar 16 '22 at 17:36

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

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

1 Answers1