CODEWITHSUNDEEP
sqloracle-database
Myles Rankin
Myles Rankin

Reputation: 33

Oracle SQL - Selecting records into groups and filtering based on a comparison of row 1 + row 2

I've got a database that contains data on monitored manufacturing machines that has these fields within (and more) :

ID | WORK_ORDER_ID | WORK_CENTER_ID | MFGNO | ...

The records are realtime data and are entered in sequentially based on when work_order_id changes. I want to check between work orders if the MFGNO is the same but grouped based on the work_center_id.

For example:

1. 998 | 100 | 205 | TEST_MFG
2. 997 | 100 | 205 | TEST_ MFG

This would return true (or 1 row), as the mfgno's are the same.

Currently I'm able to do this for each work_center_id individually like this:

SELECT * FROM
(
  select * FROM (select ID, WORKORDER_ID, TIMESTAMP, MFGNO from 
  HIST_ILLUM_RT where WORK_CENTER_ID = 5237
  ORDER BY ID desc) where rownum = 1
)
where MFGNO = ( 
    SELECT mfgno FROM
     (
     select * FROM (select ID, WORKORDER_ID, TIMESTAMP, MFGNO from 
     HIST_ILLUM_RT where WORK_CENTER_ID = 5237 
      ORDER BY ID desc
     ) where rownum < 3 order by id asc
    ) where rownum = 1
)

This produces either 0 rows if there are no current back to back MFGNO's, then 1> if there is.

This way I have to write this expression for each individual work_center_id (there's about 40). I want to have an expression that checks the top two rows of each grouped work_center_id and only returns a row if the MFGNO's match.

For example:

1. 998 | 101 | 205 | TEST_MFG
2. 997 | 098 | 206 | SomethingElse
3. 996 | 424 | 205 | TEST_MFG
4. 995 | 521 | 206 | NotAMatch
5. 994 | 123 | 205 | Doesn'tCompareThis
6. 993 | 664 | 195 | Irrelevant

For this it would only return 1, as only the work_center_id = 205 has a back to back (row 1&2) MFGNO, compared to 206 which doesn't for example.

I'm running Oracle 11g which seems to be limiting me, but I am unable to upgrade or find a work around to create this expression in this current version.

Upvotes: 1

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

I think you want lag() and some logic:

select count(*)
from (select t.*,
             lag(MFGNO) over (partition by WORK_CENTER_ID order by id) as prev_mfgno
      from t
     ) t
where prev_mfgno = mfgno

Upvotes: 0

Related Questions