SQL: How to filter all the rows containing a certain product based on the value of a third column

Question

I have a table with three columns: job_id, part, and time, which represent the ID of a shop job, the part manufactured in that job, and the time taken for that job, respectively.

Job_id	Part	Time
1	A	0
2	F	0
3	F	0
4	C	10
5	B	0
6	D	5
7	F	0
8	B	0
9	E	0
10	F	10

In this table, a higher job_id corresponds to a more recent job. For example, for Part F, job_id = 10 represents the most recent job, while job_id = 2 is the oldest.

I need to select all the rows for a part where the most recent job (i.e., the row with the highest job_id) has a 'time' of 0. For instance:

Part A: Select it because its most recent job (job_id = 1) has time = 0.

Part B: Select both rows because its most recent job (job_id = 8) has time = 0.

Part F: Do not select any rows because its most recent job (job_id = 10) does not have time = 0.

Desired result:

Job_id	Part	Time
1	A	0
8	B	0
5	B	0
9	E	0

Since we're selecting rows from different parts based on their most recent job's time, I believe I need to partition the data by part. To achieve this, I have used the ROW_NUMBER() function to assign row numbers to each group.

Job_id	Part	Time	row_number
1	A	0	1
8	B	0	1
5	B	0	2
4	C	10	1
6	D	5	1
9	E	0	1
10	F	10	1
7	F	0	2
3	F	0	3
2	F	0	4

However, I am unsure how to use this partitioned data to extract the desired result.

I am using MySQL for this task.

Answer 1

with 
src (job_id, part, time) AS (
   select 1,'a',0 union all
   select 2,'f',0 union all 
   select 3,'f',0 union all 
   select 4,'c',10 union all 
   select 5,'b',0 union all 
   select 6,'d',5 union all 
   select 7,'f',0 union all 
   select 8,'b',0 union all 
   select 9,'e',0 union all
   select 10,'f',10   
),
get_last (job_id, part, time, last_time) AS (
  SELECT job_id, part, time,
         FIRST_VALUE(time) OVER (PARTITION BY part ORDER BY job_id DESC)
  FROM src
)
SELECT job_id, part, time
FROM get_last
WHERE last_time = 0
ORDER BY part, job_id

job_id	part	time
1	a	0
5	b	0
8	b	0
9	e	0

fiddle

PS. Thanks to Sergey for source script.