How to leave only the last row in sql?

Question

SQL Server

MS SQL
There is a journal, a flat table:
Id (PK)
ForeignKey
Datetime
Text

It is necessary to leave one record for each ForeignKey, which has a maximum date, and delete the rest.

Answer 1

You can use ROW_NUMBER:

SELECT Id, ForeignKey, Datetime, Text FROM (
  SELECT
    ROW_NUMBER() OVER(PARTITION BY ForeignKey ORDER BY DateTime DESC) as rn,
    *
  FROM table
  WHERE
    rn = 1
);