find the process of a unit that happened before a specific process step

Question

We are using php/postgresql for process control at our company. The company is repairing units, and the repair process is followed by the process control system.

The unit table stores the information about the individual units.

• unit.id : the id of the unit
• unit.sn : the serial number of the unit

The unit_process table stores every process step the unit went through in it's lifetime.

• unit_process.id : the id of the process step
• unit_process.unit_id : the id of the unit
• unit_process.process_id : the id of the process.

(the real tables have more columns)

an sql to find a unit's process steps in chronological order:

SELECT process_id 
FROM unit 
INNER JOIN unit_process on unit.id=unit_process.unit_id
ORDER BY unit_process.id

I would like to create a query which finds all process steps which are happened before process steps that have a specific process_id

So I have a process_id, I need to find all unit_process rows that happened just before a process which has that process_id

Currently I am using about the worst method I can imagine.

For example let's say, I need all previous process steps which happened before processes that has process_id=17

First, I list the units which have processes with process_id=17

SELECT unit.id
FROM unit
INNER JOIN unit_process on unit.id=unit_process.unit_id
WHERE process_id=17

Then I store them in a php array.

Secondly, I use a php foreach which contains the following query for every unit id I got from the previous query.

SELECT id,process_id
FROM unit_process
WHERE unit_id=$unit_id
ORDER BY id

After this, I could easily find out using php what process was before the process_id=17 process step, since I just have to find the biggest id value which is still lower than the (process_id=17)'s.

It could use up to thousands of querys in that foreach, and I want to change that, but I don't have enough SQL knowledge to do it on my own.

Is it possible to do this in a single query?

Sample input greens are needed, yellows are the process_id=17 units. Notice that there is a process_id=17 wich is also needed :

Sample output:

I hope I didn't messed up the samples, I just came up with these for example.

Answer 1

I think you are looking for lead/lag window functions. See an experts explanation here: How to compare the current row with next and previous row in PostgreSQL?

Answer 2

I am not familiar with postgresql, so my answer is using SQL Server syntax,

Declare @fkProcessID int = 17
select ab.unit_id,ab.id from unit_process as a
outer apply
    (
        select top 1 * from unit_process as b
        where a.id > b.id
        order by b.id desc
    )as ab
where a.process_id = @fkProcessID
order by ab.unit_id

For DEMO

postgreSQL,Just missed out on variable declaration, but all else is working.

select ab.unit_id,ab.id from unit_process as a
 cross join LATERAL
    (
        select * from unit_process as b
        where a.id > b.id
        order by b.id desc
        LIMIT 1
    )as ab
where a.process_id = 17
order by ab.unit_id;

new DEMO

Hope it helps you!

Answer 3

Try something like:

select p2.unit_id, p2.id
from unit_process p1
join unit_process p2 on p1.unit_id = p2.unit_id and p1.id > p2.id
where p1.process_id=17
order by p2.unit_id, p2.id