Compare the Current Row to Next Row in SQL

Using a CTE one can compare the current row of data to the next row or even the previous row.  For example we can use the sql from the OrgCode Post.

 SELECT * FROM PS_POSITION_DATA WHERE ORGCODE LIKE (SELECT ORGCODE||'%' FROM PS_POSITION_DATA X WHERE X.POSITION_NBR = ? AND X.ORGCODE > ' ')  ORDER BY ORGCODE;


If we modify this sql just to select the current rownumber, position number, the length of ORGCODE divided by 3( I will tell you why in a second)  then we have the following SQL below:


With Cte  As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Orglevel From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '02010087' And X.Orgcode > ' ')  Order By Orgcode)
 select * from CTE


This sql will return the following results:

Where ORGLEVEL is the degrees of separation from the Top Position, i.e. CEO.  And the Rownumber column will be used by us to join the current sql row to the next sql row.  This will allow us to determine if the next row reports the current row or if this row it is a peer.


  With Cte  As (Select Rownum Rownumber, Position_Nbr, Length(Orgcode)/ 3 As Level1    From Ps_Position_Data Where Orgcode Like (Select Orgcode||'%' From Ps_Position_Data X Where X.Position_Nbr = '02010087' And X.Orgcode > ' ')  Order By Orgcode)
 Select c1.rownumber, c2.rownumber, C1.Level1 , C2.Level1,  C1.Position_Nbr Cur_Position_Nbr, C2.Position_Nbr Prev_Position_Nbr,C1.Level1 - C2.Level1 separation  From Cte C1 Left Outer Join Cte C2 On C1.Rownumber = C2.Rownumber + 1 order by c1.rownumber;

This sql allows us to use the rownumber to join the current row number to the next in SQL.