How to override past records in SQL?-CodePudding

I have a SQL table where one field can have the same value in multiple records. For example:

I would like to select the EndTime and UserId of each UserID once. If there are multiple records with the same UserID I want to select the most recent record and override the past ones.

I'm working with Sqlite in python.

CodePudding user response：

You can use a window function such as ROW_NUMBER()

SELECT EndTime, userID
  FROM
  (
   SELECT ROW_NUMBER() OVER (PARTITION BY userID ORDER BY StartTime DESC) AS rn,
          t.* 
     FROM t 
  ) AS tt
 WHERE rn = 1

where grouping by userID is provided by PARTITION BY userID, and picking the recent values is provided through sorting descendingly by StartTime

Demo

CodePudding user response：

All you need is aggregation:

SELECT UserID, MAX(StartTime) StartTime, EndTime
FROM tablename
GROUP BY UserID;

because SQLite will return for each UserID the row with the max StartTime.

See the demo.