How to fetch the last updated time of a record? I have a table with 5 columns in the following format:

| id | value1 | value2 | value3 | last_updated |
------------------------------------------------


The value in the columns value1, value2 and value3 can change or remain same. Those values comes from an external process, which makes a lot of entries in that table. It records the changes in the last_update column. The value of the value* columns can remain the same. I am trying to find the first event (last_update) time when any of the value1, 2, 3 is changed for first time.
You task has only meaning if the "id" is not a primary key, but a foreign key of your table. It means, that your table has multiple rows with the same id.

Thus, your table is essentially a log.

To get the first row of all ids, you can use the MIN() aggregate function, grouped the tables with ids:

SELECT MIN(last_update) FROM yourTable GROUP BY id;


However, if it is possible that all the values remain the same if a new log entry arrives into your table, then the situation is a little bit harder.

SQL is not very effective is we want to compare consecuting rows, although it is possible.

A possible solution would be using windowing functions. Essentially, we count the timeline of all the records, and then compare all of them to their consecuting one.

WITH Raw AS (
  SELECT *, RANK() OVER (PARTITION BY Id ORDER BY last_update) AS EventNo
  FROM yourTable
)
SELECT Prev.Id, MIN(Next.LastUpdate) AS FirstChangeTime
FROM Raw AS Prev, Raw AS Next
WHERE
  Prev.Id = Next.Id
  AND
  Prev.EventNo + 1 = Next.EventNo
  AND
  ( Prev.Value1 &lt;&gt; Next.Value1
    OR
    Prev.Value2 &lt;&gt; Next.Value2
    OR
    Prev.Value3 &lt;&gt; Next.Value3
  )
GROUP BY Prev.Id;


Note, this solution is the simplest what came in my mind now. It has a terrible performance and a resource waste. Use it only for data mining related tasks.

To do the same effectively requires more advanced technics over the limit of the current answer.