Question 419002 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0 
    with table_1(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   
)

SELECT T.JOB_ID,Z.PART, T.TIME FROM
  (
     SELECT XQ.JOB_ID,XQ.PART,XQ.TIME FROM
      (
         select t.job_id,t.part,t.time,
          ROW_NUMBER()over(partition by t.part order by t.job_id desc)as xcol
         from table_1 as t
      )AS XQ 
      WHERE XQ.xcol=1 AND XQ.TIME=0
   )Z
  JOIN TABLE_1 AS T ON Z.PART=T.PART

    Please try this one. I tired my best to implement the suggested solution

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search