MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.22


CREATE TABLE people 
(
  people_id INT, 
  t_timestamp TIMESTAMP, 
  favourite_number INT
);
INSERT INTO people VALUES 
(1,	'2020-11-04 17:40:33',	14),
(2,	'2020-12-06 21:19:41',	59),
(3,	'2020-12-10 18:46:11',	14),
(4,	'2020-12-15 00:01:53',	7);
SELECT * FROM people;


people_id
t_timestamp
favourite_number


1
2020-11-04 17:40:33
14

2
2020-12-06 21:19:41
59

3
2020-12-10 18:46:11
14

4
2020-12-15 00:01:53
7


SELECT 
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
  people_id,
  t_timestamp,
  favourite_number
FROM people
ORDER BY people_id, t_timestamp, favourite_number;


rn
people_id
t_timestamp
favourite_number


1
1
2020-11-04 17:40:33
14

1
2
2020-12-06 21:19:41
59

2
3
2020-12-10 18:46:11
14

1
4
2020-12-15 00:01:53
7


SELECT * FROM
(
  SELECT 
    ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn,
    people_id,
    t_timestamp,
    favourite_number
  FROM people
--  ORDER BY people_id, t_timestamp, favourite_number
) AS tab
WHERE rn = 1
ORDER BY favourite_number, t_timestamp, favourite_number;


rn
people_id
t_timestamp
favourite_number


1
4
2020-12-15 00:01:53
7

1
1
2020-11-04 17:40:33
14

1
2
2020-12-06 21:19:41
59


SELECT 
  people_id,
  t_timestamp,
  favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp) AS rn
FROM people
ORDER BY people_id;


people_id
t_timestamp
favourite_number
has_original_number
rn


1
2020-11-04 17:40:33
14
1
1

2
2020-12-06 21:19:41
59
1
1

3
2020-12-10 18:46:11
14
0
2

4
2020-12-15 00:01:53
7
1
1

people_id	t_timestamp	favourite_number
1	2020-11-04 17:40:33	14
2	2020-12-06 21:19:41	59
3	2020-12-10 18:46:11	14
4	2020-12-15 00:01:53	7