In a transaction table, how would you keep only the latest record for each entity?

🟡 Medium Conceptual Junior level

1Times asked

Jul 2026Last seen

Jul 2026First seen

💡 Model Answer

Use a window function to rank rows per entity by timestamp, then select the top row. Example:

sql

SELECT *
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY entity_id ORDER BY transaction_date DESC) AS rn
  FROM transactions t
) sub
WHERE rn = 1;

This returns the most recent transaction per entity. Alternatively, enforce it with a trigger that deletes older rows on insert, or maintain a separate view that always shows the latest record.

This answer was generated by AI for study purposes. Use it as a starting point — personalize it with your own experience.

🎤 Get questions like this answered in real-time

Assisting AI listens to your interview, captures questions live, and gives you instant AI-powered answers on a discreet on-screen overlay.

Get Assisting AI — Starts at ₹500