CODEWITHSUNDEEP
sqloracle-database
user3264817
user3264817

Reputation: 21

Compare nth row with n+1 th row and if it lies in range of n th row print n+1 row USNG ORACLE QUERY

I have a table like

ID     name   Start_no     End_no
1       a       2           123  
2       b       3           65
3       c       191         199
4       d       201         225
5       e       220         223
6       f       221         224

I tried:

SELECT  *   
  FROM IV_STOCK_DETAILS a
 where  a.STOCK_START_NO in ( select b.stock_end_no
                                from IV_STOCK_DETAILS B
                               where b.stock_start_no between a.stock_start_no 
                                                          AND A.STOCK_END_NO 
                                 AND B.STOCK_END_NO BETWEEN a.stock_start_no 
                                                        AND A.STOCK_END_NO  
                                 AND (a.stock_start_no - A.STOCK_END_NO) !=0  
                            )
ORDER BY cod_stock_branch, stock_start_no;

so I need to check all rows one by one with its previous rows and if its start_no and end_no lies within start_no and end_no of previous one print row. In this case answer will be rows no 2, 5, 6.. I need only query.

Upvotes: 2

Views: 484

Answers (2)

Incognito
Incognito

Reputation: 3094

Here is a solution using LAG function or Oracle, which gives you access to the previous row.

And, you should only get row 2 and 5 from your table, as per the requirements you have, since row 6 has end_no as 224, which is not less than end_no of row5 which is 223

SELECT id, name, start_no, end_no 
FROM (
    SELECT id, name, start_no, end_no, 
           lag(start_no, 1, 0) over (order by id) prev_start_no ,
           lag(end_no, 1, 0) over (order by id) prev_end_no
    FROM test
) 
WHERE start_no BETWEEN prev_start_no AND prev_end_no
AND end_no BETWEEN prev_start_no AND prev_end_no;

Upvotes: 3

outis
outis

Reputation: 77400

Joins to the rescue! Join the table to itself on the previous ID and include the range test in the join.

SELECT iv.*
  FROM IV_STOCK_DETAILS AS iv
    JOIN IV_STOCK_DETAILS AS ivprev ON iv.id=ivprev.id+1
         AND ivprev.Start_no <= iv.Start_no
         AND iv.End_no <= ivprev.End_no

Upvotes: 2

Related Questions