MySql Set Column on First Appearance of Value I have a select that returns something that looks like the following:




PeopleId
Timestamp
FavoriteNumber




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




Given this information, I want to, for every row, check if the person's favorite number is the first appearance of that number and if so return 0, if not, return  That would make a table that looks like:




PeopleId
Timestamp
FavoriteNumber
HasOriginalNumber




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


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


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


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




Assume the query up to this point has been:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber From people p

The second table could be achieved by doing:
Select p.PeopleId, p.Timestamp, p.FavoriteNumber, 
    ((Select Min(x.Timestamp) from people x where x.FavoriteNumber = p.FavoriteNumber) = p.TimeStamp) as 
    HasOriginalColor)
    From people p

However, I think that query will be very inefficient for large tables and seems like overkill. Is there an easier and faster way to do this?
This is a classic example of where window functions come in handy! In this case, we shall use the ROW_NUMBER() function.
To answer your question, I did the following (see fiddle here):
The table:
CREATE TABLE people 
(
  people_id INT, 
  t_timestamp TIMESTAMP, 
  favourite_number INT
);

Just a word of advice, you should NEVER call your fields (or your tables) using SQL keywords - it makes debugging more complex and error-prone and your apps less portable and it's bad practice generally.
Populate it:
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);

Then run the query (I'll go through the logic step-by-step):
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;

Result:
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

So, we can see by inspection that we want all of the records where rn = 1.
We now have to do a SUBSELECT (you can't put window functions in WHERE clauses):
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;

Result:
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

Now, these are the records we're interested in, but it's not quite the result you want.
We obtain this by using a CASE function, and since we're SELECTing all records, we can eliminate the SUBSELECT:
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;

Result:
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

Which, excepting rn is the result you want!
This is an example of a class of problems known as greatest-n-per-group - it's a dba.stackexchange tag - look at some of the questions in there and I would strongly urge you to get to know window functions - they will repay any effort you put into them many times over!
I modified your query in this way:
SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p;

The result is the same!
Performance analysis: (MySQL)
I then looked at the performance of both queries using the new EXPLAIN ANALYZE &lt;followed by query text&gt; functionality available in MySQL version &gt;= 8.18 (as explained here and here). For brevity, I'm only showing the plans - see the revised fiddle for the whole thing. I also make use of the MySQL profiling capability as explained here.
(caveat: I am not an expert on query plans - but I think I know a bit - any corrections, references, URLs appreciated):
I turned on SET PROFILING = 1;
Then, after running the two queries, I looked at the profiles (some of the query text is truncated):
SHOW PROFILES; 

=================================

Query_ID    Duration    Query

1           0.00078575  

SELECT 
  p.people_id, p.t_timestamp, p.favourite_number,
  CASE
  WHEN
    (
      SELECT 
        MIN(x.t_timestamp) FROM people x WHERE x.favourite_number = p.favourite_number
    ) = p.t_timestamp
    THEN 1
    ELSE 0
  END AS has_original_number
FROM people p
ORDER BY p.people_id

=======================================

2           0.00062000  

SELECT 
  p.people_id,
  p.t_timestamp,
  p.favourite_number,
    CASE
      WHEN 
        ROW_NUMBER() OVER (PARTITION BY p.favourite_number ORDER BY p.t_timestamp) = 1
      THEN 1
      ELSE 0
    END AS has_original_number,
  ROW_NUMBER() OVER (PARTITION BY favourite_number ORDER BY t_timestamp)

_
I ran this several times (typical run shown) and the result consistently shows that the ROW_NUMBER() query is faster (by ~ 20% - frequently more) than the MIN() one - this seems logical (to me) since the MIN() query is SELECTing from the people table twice! The difference is larger if I run the MIN() query second. This effect is also seen if the queries are run in the opposite order.
However, when I ran the query plans, I got a surprise:
-&gt; Sort: people.people_id  (actual time=0.064..0.065 rows=4 loops=1)
    -&gt; Table scan on &lt;temporary&gt;  (actual time=0.000..0.001 rows=4 loops=1)
        -&gt; Temporary table  (actual time=0.057..0.058 rows=4 loops=1)
            -&gt; Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.043..0.049 rows=4 loops=1)
                -&gt; Table scan on &lt;temporary&gt;  (actual time=0.000..0.001 rows=4 loops=1)
                    -&gt; Temporary table  (actual time=0.040..0.042 rows=4 loops=1)
                        -&gt; Window aggregate: row_number() OVER (PARTITION BY people.favourite_number ORDER BY people.t_timestamp )   (actual time=0.031..0.036 rows=4 loops=1)
                            -&gt; Sort: people.favourite_number, people.t_timestamp  (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
                                -&gt; Table scan on people  (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)

2nd Query (yours modified):
-&gt; Table scan on p  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-&gt; Select #2 (subquery in projection; dependent)
    -&gt; Aggregate: min(x.t_timestamp)  (actual time=0.036..0.037 rows=1 loops=4)
        -&gt; Filter: (x.favourite_number = p.favourite_number)  (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
            -&gt; Table scan on x  (cost=0.35 rows=4) (actual time=0.008..0.031 rows=4 loops=4)

The performances are roughly comparable - a bit of variability from run to run.
However, because you're returning the whole table, the optimiser will ignore indexes and scan the whole table - Table Scan on people - note that for your query, it performs 2 Table Scans whereas for mine it only performs 1 for mine (temporary tables excluded - in memory?).
IMHO, this means that as the row-count of the people table grows, the query which performs 2 table scans will slow down as a function of O(n2) but the other query will only slow down as a function of O(n). So, long-term, I think you'll be better off with my query - but, I am open to correction on this!