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 )