I have an existing SQL table a bit like this
| Question | Answer | AnsWhen | AnsWho |
|---|---|---|---|
| Q1 | Red | 2022-03-01 | Rod |
| Q1 | Green | 2022-03-20 | Freddie |
| Q2 | Blue | 2022-04-05 | Jane |
| Q2 | Yellow | 2022-04-01 | Rod |
| Q3 | Green | 2022-04-05 | Jane |
| Q3 | Green | 2022-04-04 | Rod |
| Q3 | Green | 2022-04-06 | Freddie |
I need to remove duplicate questions and only keep the latest data, so the finished table would look like
| Question | Answer | AnsWhen | AnsWho |
|---|---|---|---|
| Q1 | Green | 2022-03-20 | Freddie |
| Q2 | Blue | 2022-04-05 | Jane |
| Q3 | Green | 2022-04-06 | Freddie |
CodePudding user response：
If your database support it, ROW_NUMBER can be used here:
WITH cte AS (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Question ORDER BY AnsWhen DESC) rn
FROM yourTable t
)
SELECT Question, Answer, AnsWhen, AnsWho
FROM cte
WHERE rn = 1;
CodePudding user response：
I've not tested, something like so.
with cteQuestions
as
(
select a.question,max(a.AnsWhen) as MAX_DATE
from tblAnswers as a
group by a.question
)
select q.question,a.answer,q.MAX_DATE,a.AnsWho
from cteQuestions as q
left join tblAnswers as a on q.question=a.question and q.max_date=a.AnsWhen