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 SELECT
ing 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 <followed by query text>
functionality available in MySQL version >= 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 SELECT
ing 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:
-> Sort: people.people_id (actual time=0.064..0.065 rows=4 loops=1)
-> Table scan on <temporary> (actual time=0.000..0.001 rows=4 loops=1)
-> Temporary table (actual time=0.057..0.058 rows=4 loops=1)
-> 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)
-> Table scan on <temporary> (actual time=0.000..0.001 rows=4 loops=1)
-> Temporary table (actual time=0.040..0.042 rows=4 loops=1)
-> 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)
-> Sort: people.favourite_number, people.t_timestamp (cost=0.65 rows=4) (actual time=0.027..0.027 rows=4 loops=1)
-> Table scan on people (cost=0.65 rows=4) (actual time=0.013..0.016 rows=4 loops=1)
2nd Query (yours modified):
-> Table scan on p (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: min(x.t_timestamp) (actual time=0.036..0.037 rows=1 loops=4)
-> Filter: (x.favourite_number = p.favourite_number) (cost=0.35 rows=1) (actual time=0.009..0.033 rows=2 loops=4)
-> 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 Scan
s 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!