clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2335815 fiddles created (27460 in the last week).

WITH yourTable AS ( SELECT 'xa220' AS UNIQUE_ID, 1 AS ID, 'John Smith' AS NAME, 'Berlin' AS CITY, date '2020-05-01' AS "DATE" FROM dual UNION ALL SELECT 'xa195', 1, 'John Smith', 'Berlin', date '2020-03-01' FROM dual UNION ALL SELECT 'xa111', 1, 'John Smith', 'München', date '2020-01-01' FROM dual UNION ALL SELECT 'xa106', 2, 'James Brown', 'Atlanta', date '2018-04-04' FROM dual UNION ALL SELECT 'xa100', 2, 'James Brown', 'Boston', date '2017-12-10' FROM dual UNION ALL SELECT 'xa76', 3, 'Emily Wolf', 'Shanghai', date '2016-11-03' FROM dual UNION ALL SELECT 'xa20', 3, 'Emily Wolf', 'Shanghai', date '2016-07-03' FROM dual UNION ALL SELECT 'xa15', 3, 'Emily Wolf', 'Tokyo', date '2014-02-22' FROM dual UNION ALL SELECT 'xa12', 3, 'Emily Wolf', NULL, date '2014-02-22' FROM dual ), cte AS ( SELECT t.*, LAG(CITY, 1, CITY) OVER (PARTITION BY ID ORDER BY "DATE") LAG_CITY FROM yourTable t ) SELECT ID, NAME, CITY, LAG_CITY AS PREVIOUS_CITY FROM cte WHERE CITY <> LAG_CITY OR CITY IS NULL AND LAG_CITY IS NOT NULL OR CITY IS NOT NULL AND LAG_CITY IS NULL ORDER BY ID, "DATE" DESC;
ID NAME CITY PREVIOUS_CITY
1 John Smith Berlin M??nchen
2 James Brown Atlanta Boston
3 Emily Wolf Shanghai Tokyo
3 Emily Wolf Tokyo
 hidden batch(es)