Returning only most recent audit rows using concatenation of other columns to find the correct grouping

Question

I am stuck on what I thought would be a fairly straightforward query in SQL Server (I'm using 2018)

I have a table (AUDIT_TABLE) that I have read only access to which looks like this:

ID	DimensionA	DimensionB	DimensionC	Amount	UserID	Timestamp
1	ABC	DEF	GHI	100	Mark	2022/01/01 12:00:00
2	ABC	DEF	GHI	10	James	2022/01/01 06:00:00
3	ABC	DEF	GHI	250	Mark	2022/01/01 02:00:00
4	JKL	MNO	PQR	250	Bob	2022/01/01 13:00:00
5	JKL	MNO	PQR	100	Dave	2022/01/01 12:00:00

What I'm trying to find is the most recent entries in an audit table and the grouping needs to be determined by combining multiple columns. eg All rows where (Dimension A + Dimension B + Dimension C) are the same are considered to be the same record and we want to find the most recent entry to it. In the table above IDs 1, 2 & 3 are the same (ABC+DEF+GHI), and IDs 4 & 5 are the same (JKL+MNO+PQR).

So using the above example table I am trying to write a select statement to return this result:

Dimension A	Dimension B	Dimension C	Amount	UserID	Timestamp
ABC	DEF	GHI	100	Mark	2022/01/01 12:00:00
JKL	MNO	PQR	250	Bob	2022/01/01 13:00:00

I have tried this query (amongst many other combinations) and just can't get the result I'm after:

SELECT DimensionA, DimensionB, DimensionC, Amount, 
UserID, Max(Timestamp), (DimensionA + DimensionB + DimensionC) AS POV
FROM AUDIT_TABLE
GROUP BY POV

Any help would be greatly appreciated.

Answer 1

Does this work?

select DimensionA,
       DimensionB,
       DimensionC,Amount,UserID,TimeStamp
from (select DimensionA,
             DimensionB,
             DimensionC,Amount,UserID,TimeStamp,
             row_number() over(partition by DimensionA,DimensionB,DimensionC order by Timestamp desc) as rn
      from AUDIT_TABLE ) as T
where rn = 1     

DB<>Fiddle

Reference