This question has already been answered:

Good day! My SQL table consists of columns [hardware] [state] [comment] [date] . In the equipment column devices: under the code from 1 to 50, Status column: on, off, 1 speed, 2 speed. Comment column: free text. Date column: time of the status change. Any change in the state of the device is fixed by the new record whether it is on, set at different speeds or off. The question is: how from this table to display the current latest status of all exclusively included devices?

Reported as a duplicate member of PashaPash ♦ 14 Nov '17 at 17:17 .

A similar question was asked earlier and an answer has already been received. If the answers provided are not exhaustive, please ask a new question .

  • The simplest (but not the most effective) is to get in the subquery for each device the time of the last state change, and select records on it and state. - Akina

1 answer 1

select * from ( select A.*, row_number() over(partition by [оборудование] order by [дата] desc) RN from table A ) B where RN=1