How to union current table and history table order by recent edit

I am currently working on preparing audit table and the output structure I am planning to have is, latest record from current table and followed by associated(id) records from history table for all the records. So that user can see the latest changes and its history. At present I am using UNION of two tables and ORDER BY id but that is not helping me get latest current record to top.

How can I achieve this?

Posts(current table)

+----+------+-------------------------+
| id | name | lastedited              |
+----+------+-------------------------+
| 1  | A    | 2021-01-01T10:00:00.626 |
+----+------+-------------------------+
| 2  | B    | 2021-03-01T10:00:00.626 |
+----+------+-------------------------+
| 3  | C    | 2021-02-01T10:00:00.626 |
+----+------+-------------------------+

Posts(history table)

+----+------+-------------------------+
| id | name | lastedited              |
+----+------+-------------------------+
| 1  | A    | 2021-01-01T09:00:00.626 |
+----+------+-------------------------+
| 2  | B    | 2021-01-01T10:00:00.626 |
+----+------+-------------------------+
| 3  | C    | 2021-02-01T08:00:00.626 |
+----+------+-------------------------+

Expected output(latest changed record appears first and then the associated records from history tables)

+----+------+-------------------------+
| id | name | lastedited              |
+----+------+-------------------------+
| 2  | B    | 2021-03-01T10:00:00.626 |
+----+------+-------------------------+
| 2  | B    | 2021-01-01T10:00:00.626 |
+----+------+-------------------------+
| 1  | A    | 2021-01-01T10:00:00.626 |
+----+------+-------------------------+
| 1  | A    | 2021-01-01T09:00:00.626 |
+----+------+-------------------------+
| 3  | C    | 2021-02-01T10:00:00.626 |
+----+------+-------------------------+
| 3  | C    | 2021-02-01T08:00:00.626 |
+----+------+-------------------------+

I am using MS SQL 2014

Answer

You can use union all with order by and window functions:

select *
from ((select id, name, lastupdated
       from posts
      ) union all
      (select id, name, lastupdated
       from post_history
      )
     ) p
order by max(lastupdated) over (partition by id) desc,
         id,
         lastupdated desc

Advertisement

Answer