CODEWITHSUNDEEP
sqlsql-serversql-server-2008
bob269
bob269

Reputation: 113

Compare two rows in two columns

I have a table for example like below

column1    column2
 110         100
  50         125
 120          80

I want a selection in such a way that i will get something like this

column1    column2    difference
 110         100           0
  50         125          50
 120          80           5

or just to be able to identify the difference between first row of column2 and second row of column1

Upvotes: 0

Views: 129

Answers (2)

Juan Ruiz de Castilla
Juan Ruiz de Castilla

Reputation: 974

Another way, could be this:

SELECT TB.COLUMN1,TB.COLUMN2,
(ISNULL(TB2.COLUMN2,TB.COLUMN1)-TB.COLUMN1) AS 'DIF'
FROM 
(SELECT COLUMN1,COLUMN2,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS 'R' FROM TEST ) TB
LEFT JOIN 
(SELECT COLUMN1,COLUMN2,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS 'R' FROM TEST ) TB2
ON TB.R = TB2.R

Until before the post I didn't know how dont let row_number 'order by' affected the query, but based on the above answer, now I Know it, using select null ;) thank you @Felix Pamittan

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this with a LEFT JOIN:

SQL Fiddle

WITH Cte AS(
    SELECT *,
        rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    FROM tbl
)
SELECT
    t1.*,
    difference = ISNULL(t2.column2 - t1.column1, 0)
FROM cte t1
LEFT JOIN Cte t2
    ON t1.rn = t2.rn + 1

Since there is no column to indicate the order, I added a ROW_NUMBER. Modify the ORDER BY clause to your preference.

Upvotes: 2

Related Questions