MySQL 5.7 - getting latest entry(s) from specific combination of columns

Question

Hello dear MySQL users!

i try to solf this since a while but everything i tried does not seem to work. I'm sorry if somewhere is already an answer for that but i know know how to search for.

Its not so easy to explain in words so i use this table (called tbl ) to explain it:

ID	SerNo	TypeOfChange	InfoAboutChange	TimestampOfChange
1	1	Name	DN01	2020/12/01
2	1	IPAddress	192.168.1.1	2020/12/01
3	1	Name	DNX1	2020/12/02
4	2	Name	DN02	2020/12/04
5	2	IPAddress	192.168.1.2	2020/12/04
6	1	Name	DNC1	2020/12/05

Now i want to get the latest state of each SerNo. It should look like this:

ID	SerNo	TypeOfChange	InfoAboutChange	TimestampOfChange
2	1	IPAddress	192.168.1.1	2020/12/01
6	1	Name	DNC1	2020/12/05
4	2	Name	DN02	2020/12/04
5	2	IPAddress	192.168.1.2	2020/12/04

i found out how to get the latest entry of each TypeofChange but its the very latest change ignoring the SerNo of a device:

select TypeOfChange, max(TimestampOfChange) from tbl group by TypeOfChange

i thing i need a combination of grouping SerNo and after that finding all typeofchange, group them and finding the latest version with max()

but i absolutely have no clue how to to that.

Thank you and stay healthy!

Answer 1

You can do:

select t.*
from mytable t
where timestampofchange = (
    select max(t1.timestampofchange)
    from mytable t1
    where t1.typeofchange = t.typeofchange and t1.erno = t.serno
)