Select max value of timestamp grouped by name, jobId and Action

Question

I have my table as shown below(in the upper part of the image), I want to select the max timestamp of the job performed by a user. For example, User1 performed many Process1 activities and we need to select the latest activity which is Start for Process1 on 5.11.2020.

I can not use the group by as I would ahve to include all the column names in select and group by which would just get all the coumns as the timestamp is unique per employee.

How can I achive this?

Sample Data:

| User  | Activity | Timestamp       | Action |
|-------|----------|-----------------|--------|
| User1 | process1 | 1.11.2020 10:00 | Start  |
| User1 | process1 | 1.11.2020 10:30 | Stop   |
| User1 | process1 | 1.11.2020 11:00 | Start  |
| User1 | process1 | 1.11.2020 11:30 | Start  |
| User1 | process1 | 5.11.2020 10:00 | Start  |
| User2 | process1 | 5.11.2020 10:05 | Start  |
| User2 | process1 | 5.11.2020 10:30 | Stop   |
| User2 | process2 | 5.11.2020 10:45 | Start  |

The output should be:

| User  | Activity | LastAction |
|-------|----------|------------|
| User1 | process1 | Start      |
| User2 | process1 | Stop       |
| User2 | process2 | Start      |

Answer 1

You can use row_number() to identify the latest row per user and activity:

select *
from (
    select t.*,
        row_number() over(partition by user, activity order by timestamp desc) rn
    from mytable t
) t
where rn = 1