I am doing a project to find a list of paired left right parts in a table. The table contains a column of Unique Part IDs and a column of Left or Right attributes.
If there is a right left pair in the table then the left ID will appear one Unique Part ID before the Right (ID 4 will be Left and ID 5 will be Right). However, there are many Unique IDs between each Left Right Pair that do not have a pair. I'm trying to write a query to find all Left and Right pairs within the table.
For example (currently Both ID and LR are VARCHAR2)
ID LR
1 L
2 R
5 R
9 R
22 R
34 L
35 R
38 L
91 L
92 R
and I need the query to return
ID LR
1 L
2 R
34 L
35 R
91 L
92 R
My current line of thinking is a conditional query to select all IDs and LR when LR is R the ID - 1 exists and that LR is L but I'm not sure...any help would be greatly appreciated! Thank you.
Set operations is one way you could do this.
(edited to rewrite query as per comments - it now handles leading zeros in the varchar ID field)
WITH matches AS (
SELECT to_number(ID) AS ID_integer
FROM base_table
WHERE LR = 'L'
INTERSECT
SELECT to_number(ID)-1
FROM base_table
WHERE LR = 'R'
)
SELECT bt.ID, bt.LR, m.ID_integer
FROM base_table bt, matches m
WHERE LR = 'L'
AND to_number(bt.ID) = m.ID_integer
UNION
SELECT bt.ID, bt.LR, m.ID_integer
FROM base_table bt, matches m
WHERE LR = 'R'
AND to_number(bt.ID)-1 = m.ID_integer
ORDER BY 3, 2;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments