Case for every second row in a resultset SQL server 2008

Question

I have a resultset of many rows and I want to write a case expression for comparing every second row in my result without hard coding the rownumber.

Every second row in my result is connected to each other.

Example:

1 Number1: 2345 Number2: 1111

2 Number1: 3456 Number2: 2222

3 Number1: 4567 Number3: 3333

4 Number1: 5678 Number4: 4444

Want to compare row 1 and 2 /3 and 4 and so on..

Any suggestion? I'm using SQL Developer 2008.

Answer 1

insert same data to two diffrent #table and compare using while loop as below,

DECLARE @min INT =1,
                  @max INT ,@Table1_number1 INT,@Table1_number2 INT,@Table2_number1 INT,@Table2_number2 INT
CREATE TABLE #Table1(ID INT IDENTITY(1,1),
                            number1 INT,number2 INT)
CREATE TABLE #Table2(ID INT IDENTITY(1,1),
                            number1 INT,number2 INT)
INSERT INTO #Table1(number1,
                    number2)
VALUES(1,
       1), (1,
            1), (2,
                 2)
INSERT INTO #Table2(number1,
                    number2)
VALUES(1,
       1), (1,
            1), (2,
                 2)
SELECT @max=
  (SELECT count(1)
   FROM #Table1) WHILE(@min<=@max) BEGIN
SELECT @Table1_number1=number1,
       @Table1_number2=number2
FROM #Table1
WHERE ID=@min IF(@min=@max) BEGIN
  SELECT @Table2_number1=number1,
         @Table2_number2=number2
  FROM #Table2
  WHERE ID=1 END ELSE BEGIN
  SELECT @Table2_number1=number1,
         @Table2_number2=number2
  FROM #Table2
  WHERE ID=@min+1 END

    IF(@Table1_number1=@Table2_number1
    AND @Table1_number2=@Table2_number2) BEGIN PRINT 'do something' END ELSE BEGIN PRINT '-- do something else' END
    SET @min=@min+1 END
  DROP TABLE #Table1
  DROP TABLE #Table2