Group by returning same rows in sql

Question

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

Yes, because the Answer and Seq values are all different. Groups never go smaller than the distinct values of all the columns in the GROUP BY — Caius Jard, Mar 29 '22 at 19:30
how can i tweak this query to get only the latest interaction date row ? — SSA_Tech124, Mar 29 '22 at 19:32
From the linked duplicate: `SELECT * FROM learning_table ORDER BY interaction_creation_date DESC FETCH FIRST ROW ONLY` — MT0, Mar 29 '22 at 19:34

score 0 · Accepted Answer · answered Mar 29 '22 at 19:34

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

Group by returning same rows in sql

1 Answers1