Group by returning same rows in sql

March 29, 2022

I have the following columns coming in my output from a query

select 
person_number,
event_id,
question,
answer,
seq,
event_attempt_id,
INTERACTION_CREATION_DATE
from learning_table


Person_number           Event_id            question            answer          seq  event_attempt_id       INTERACTION_CREATION_DATE
6                       123             what is your name       Reena           78      1                       11-jan-2020
6                       123             what is your name       Freid           67      2                       29-jan-2020
6                       123             what is your name       Heera           89      3                       24-jan-2020

Now I want only the max row of interaction_creation_date to come in the output

i.e. 
Person_number           Event_id            question            answer          seq  event_attempt_id       INTERACTION_CREATION_DATE
6                       123             what is your name       Freid           67      2                       29-jan-2020

when i am using the below query it is still returning all the rows

select 
person_number,
event_id,
question,
answer,
seq,
max(INTERACTION_CREATION_DATE)
from learning_table

group by person_number,
event_id,
question,
answer,
seq

>Solution :

Typically, these days, we do this "want some row in a group that has the max blahblah together with all its other values" with a nested window function:

with x as (
  select 
    person_number,
    event_id,
    question,
    answer,
    seq,
    ROW_NUMBER() OVER(PARTITION BY person_number, event_id,  question ORDER BY INTERACTION_CREATION_DATE DESC) rn
  from learning_table
) 
SELECT * FROM x WHERE rn = 1