Forward checking for a column value in another column in MSSQL. How to?
Posted on 2011-10-17
I got a table as:
What I need is checking for ID column's content whether it occurs in forward manner. What I mean is that as for the first row; id is 1 and that value is reoccuring in the 4th row as 3 1. I need to grab that line.
Similary 5 6 -> 4 5
** Please be aware 1 2 -> 2 4 is not counted as 2 4 comes later than 1 2
How can I do this in MSSQL?
P.S. What I used as a query is as:
SELECT COUNT(*) FROM MyTABLE A WHERE EXISTS (SELECT * FROM MyTABLE B WHERE B.ID <> A.ID AND B.COLX = A.ID);
SELECT A.ID, A.COLX, B.ID, B.COLX FROM MyTable A, MyTable B WHERE A.ID <> B.ID And B.COLX = A.ID;
but this query also gives back the ** case.