How to display the start and end date and the user who created the order, the user who closed it?

Question

SQL

Good afternoon! There is a table in which there are three columns: order number, update date, the user
on the update date can be recorded from 1 to infinity of date change records, depending on whether its status has changed, the order number remains unchanged, the user can also change. You need to display the order number, the date the order was created and the user who created it, the last date the order was updated, and the last user

Answer 1

because DBMS is not specified and I will write the version in general

select t1.*
from table t1
join (select nomer_zakaza, max(data_obnovleniya) max_DT, min(data_obnovleniya) min_DT
          from table
       group by nomer_zakaza) t2
on (t1.nomer_zakaza = t2.nomer_zakaza and t1.data_obnovleniya in (max_DT, min_DT))
order by t1.nomer_zakaza, t1.data_obnovleniya

you can use window functions, but it depends on the DBMS and version