Skip to content
Advertisement

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

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!

Advertisement

Answer

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
)
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement