Fetch data using group by but last updated record
I want to display records on the basis of last updated , if updated column is empty then create date
Id | SubjectId | MarksId | Entry date | Updated Date |
---|---|---|---|---|
1 | 3 | 3 | 2022-09-25 | 2022-11-25 |
2 | 3 | 3 | 2022-12-25 | null |
It should print:-
Id | SubjectId | MarksId | Entry date | Updated Date |
---|---|---|---|---|
1 | 3 | 3 | 2022-09-25 | 2022-11-25 |
If data is like below:-
Id | SubjectId | MarksId | Entry date | Updated Date |
---|---|---|---|---|
1 | 3 | 3 | 2022-09-25 | null |
2 | 3 | 3 | 2022-12-25 | null |
It should print:-
Id | SubjectId | MarksId | Entry date | Updated D开发者_如何学Cate |
---|---|---|---|---|
1 | 3 | 3 | 2022-12-25 | null |
I am writing below query but unable to fetch the same it everytime fetches the second thing even though I have updatedate present
select Id,at.SubjectId,at.marksid
from student at
INNER JOIN (
select MAX(Id) as StudentId from student
group by SubjectId,marksid
ORDER BY COALESCE(UPDATEDATE,ENTRYDATE) desc
) maxat ON at.Id=maxat.StudentId
Further info.
MySQL version 8.0
Entry date cant be null it will have data, Kind of auto generated when data inserts to this table
Please include all the details in the question so it could help future readers as well.
As per the question using row_number is one option.
select Id,
SubjectId,
MarksId,
Entrydate,
UpdatedDate
from ( select *,
row_number() over(partition by SubjectId,MarksId order by (case when UpdatedDate is null then Entrydate else Entrydate end) desc) as rn
from student
) tbl
where rn=1;
https://dbfiddle.uk/di517ZLv
精彩评论