How do I compare 2 rows from the same table SQL Server

Question

I need to create a background job that processes a table looking for rows matching on a particular id with different statuses  It will store the row data in a string to compare the data against a row with a matching id  I know the syntax to get the row data  but I have never tried comparing 2 rows from the same table before  How is it done  Would I need to use variables to store the data from each  Or some other way   Using SQL Server 2008

User · Answer

OK  after 2 years it s finally time to correct the syntax   SELECT  t1 value  t2 value FROM    MyTable t1 JOIN    MyTable t2 ON      t1 id   t2 id WHERE   t1 id    id         AND t1 status    status1         AND t2 status    status2

User · Answer

Some people find the following alternative syntax easier to see what is going on   select t1 value t2 value from MyTable t1     inner join MyTable t2 on         t1 id   t2 id where t1 id    id

User · Answer

You can join a table to itself as many times as you require  it is called a self join   An alias is assigned to each instance of the table  as in the example below  to differentiate one from another   SELECT a SelfJoinTableID FROM   dbo SelfJoinTable a        INNER JOIN dbo SelfJoinTable b          ON a SelfJoinTableID   b SelfJoinTableID        INNER JOIN dbo SelfJoinTable c          ON a SelfJoinTableID   c SelfJoinTableID WHERE  a Status    Status to filter a         AND b Status    Status to filter b         AND c Status    Status to filter c

User · Answer

I had a situation where I needed to compare each row of a table with the next row to it   next here is relative to my problem specification  in the example next row is specified using the order by clause inside the row number   function  so I wrote this  DECLARE  T TABLE  col1 nvarchar 50     insert into  T VALUES   A     B     C     D     E    select I1 col1 Instance One Col  I2 col1 Instance Two Col  from    select col1 row number   over  order by col1  as row num  FROM  T   AS I1 left join    select col1 row number   over  order by col1  as row num  FROM  T   AS I2 on I1 row num   I2 row num - 1  after that I can compare each row to the next one as I need

User · Answer

SELECT COUNT    FROM  SELECT   FROM tbl WHERE id 1 UNION SELECT   FROM tbl WHERE id 2  a  If you got two rows  they different  if one - the same

User · Answer

I had a situation where I needed to compare each row of a table with the next row to it   next here is relative to my problem specification  in the example next row is specified using the order by clause inside the row number   function  so I wrote this  DECLARE  T TABLE  col1 nvarchar 50     insert into  T VALUES   A     B     C     D     E    select I1 col1 Instance One Col  I2 col1 Instance Two Col  from    select col1 row number   over  order by col1  as row num  FROM  T   AS I1 left join    select col1 row number   over  order by col1  as row num  FROM  T   AS I2 on I1 row num   I2 row num - 1  after that I can compare each row to the next one as I need

User · Answer

OK  after 2 years it s finally time to correct the syntax   SELECT  t1 value  t2 value FROM    MyTable t1 JOIN    MyTable t2 ON      t1 id   t2 id WHERE   t1 id    id         AND t1 status    status1         AND t2 status    status2

User · Answer

SELECT   FROM A AS b INNER JOIN A AS c ON b a   c a WHERE b a    some column value

User · Answer

You can join a table to itself as many times as you require  it is called a self join   An alias is assigned to each instance of the table  as in the example below  to differentiate one from another   SELECT a SelfJoinTableID FROM   dbo SelfJoinTable a        INNER JOIN dbo SelfJoinTable b          ON a SelfJoinTableID   b SelfJoinTableID        INNER JOIN dbo SelfJoinTable c          ON a SelfJoinTableID   c SelfJoinTableID WHERE  a Status    Status to filter a         AND b Status    Status to filter b         AND c Status    Status to filter c

User · Answer

SELECT   FROM A AS b INNER JOIN A AS c ON b a   c a WHERE b a    some column value

User · Answer

OK  after 2 years it s finally time to correct the syntax   SELECT  t1 value  t2 value FROM    MyTable t1 JOIN    MyTable t2 ON      t1 id   t2 id WHERE   t1 id    id         AND t1 status    status1         AND t2 status    status2

User · Answer

SELECT COUNT    FROM  SELECT   FROM tbl WHERE id 1 UNION SELECT   FROM tbl WHERE id 2  a  If you got two rows  they different  if one - the same

User · Answer

OK  after 2 years it s finally time to correct the syntax   SELECT  t1 value  t2 value FROM    MyTable t1 JOIN    MyTable t2 ON      t1 id   t2 id WHERE   t1 id    id         AND t1 status    status1         AND t2 status    status2

User · Answer

You can join a table to itself as many times as you require  it is called a self join   An alias is assigned to each instance of the table  as in the example below  to differentiate one from another   SELECT a SelfJoinTableID FROM   dbo SelfJoinTable a        INNER JOIN dbo SelfJoinTable b          ON a SelfJoinTableID   b SelfJoinTableID        INNER JOIN dbo SelfJoinTable c          ON a SelfJoinTableID   c SelfJoinTableID WHERE  a Status    Status to filter a         AND b Status    Status to filter b         AND c Status    Status to filter c

User · Answer

Some people find the following alternative syntax easier to see what is going on   select t1 value t2 value from MyTable t1     inner join MyTable t2 on         t1 id   t2 id where t1 id    id

User · Answer

Some people find the following alternative syntax easier to see what is going on   select t1 value t2 value from MyTable t1     inner join MyTable t2 on         t1 id   t2 id where t1 id    id

User · Answer

Some people find the following alternative syntax easier to see what is going on   select t1 value t2 value from MyTable t1     inner join MyTable t2 on         t1 id   t2 id where t1 id    id

User · Answer

You can join a table to itself as many times as you require  it is called a self join   An alias is assigned to each instance of the table  as in the example below  to differentiate one from another   SELECT a SelfJoinTableID FROM   dbo SelfJoinTable a        INNER JOIN dbo SelfJoinTable b          ON a SelfJoinTableID   b SelfJoinTableID        INNER JOIN dbo SelfJoinTable c          ON a SelfJoinTableID   c SelfJoinTableID WHERE  a Status    Status to filter a         AND b Status    Status to filter b         AND c Status    Status to filter c

[sql] How do I compare 2 rows from the same table (SQL Server)?

Examples related to sql

Examples related to sql-server

Examples related to tsql