How to compare 2 rows of data and show the result in 3rd rows?

Question

I trying to calculate difference between 2 rows, e/g:

Row1: 1, "Brazil", 3, 4, 5
Row2: 2, "Brazil", 5, 6, 6
Row3: 1, "Brazil", 2, 2, 1

This is the Query example how i compare:

SELECT
    '3' as RowNumber,t1.Country ,t2.col1 -t1.col1 AS Col1Diff ,t2.col2 -t1.col2 AS Col2Diff,t2.col3 -t1.col3 AS Col3Diff 
FROM        (SELECT 1 as RowNumber,'Brazil' as Country,2 As col1,3 As col2,5 As col3)AS T1
INNER JOIN ( SELECT 2 as RowNumber,'Brazil' as Country,5 As col1,6 As col2,6 As col3)AS T2 on T1.Country = T2.Country

The first and sec row was group by result from different table, and i need the third row the show the different. i tried the inner join on same table method, but it does not behave like i wish.

I can only have the different row, and i lost the 1st and sec row. Of course i can select again and UNION back, but considering the performance issue. It just not a good idea.

Does anyone have idea on this ?

I'm using SQL Server 2008 R2

What database are you using? SQL Server or MySQL? And, please edit your question with the query you are using to generate the data. — Gordon Linoff, Apr 23 '14 at 02:41
did you see http://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field — JBC, Apr 23 '14 at 03:44
@JBC yes, but it show the different on the same row in diff column, my requirement was show in the next row. I'm still trying to modify the query — user3278315, Apr 23 '14 at 04:07
is this must be done in the sql? are u using any programming language? probably u can use a function to calculate and insert the third row in the code. it would be simpler. — cyan, Apr 23 '14 at 04:34
@cyan If possible, i wanted to do it in query instead of a function. — user3278315, Apr 23 '14 at 05:46

score 0 · Accepted Answer · answered Apr 23 '14 at 07:09

the Best Solution is to make one temporary table. insert that both row in that temporary table. than no need to that query in union multiple time. using temp table you can do that operation of union. it's better than use union with query.

as shown in below example.

Declare @tab Table (RowNumber Smallint, Country Varchar(10), Col1 Smallint, Col2 Smallint, Col3 Smallint)
Insert Into @tab
SELECT 1 as RowNumber,'Brazil' as Country,2 As col1,3 As col2,5 As col3

Insert Into @tab
SELECT 2 as RowNumber,'Brazil' as Country,5 As col1,6 As col2,6 As col3

Select *
From @tab
UNION
Select '3' as RowNumber,
    Fir.Country,
    Sec.col1 - Fir.col1 AS Col1Diff,
    Sec.col2 -Fir.col2 AS Col2Diff,
    Sec.col3 -Fir.col3 AS Col3Diff 
From @tab Fir
Left Join @tab Sec On Sec.RowNumber = 2
Where Fir.RowNumber = 1

and your output is now like this.

RowNumber   Country Col1    Col2    Col3
   1        Brazil  2        3       5
   2        Brazil  5        6       6
   3        Brazil  3        3       1

though about this solution before, wondering the performance issue, since it using the temp table — user3278315, Apr 23 '14 at 07:55
its better than use union direct on query. it's store your data in temp table. than it union. and after complete your work it release. — Ritesh Khatri, Apr 23 '14 at 08:17
It works perfectly, just personally dont like temp table on query — user3278315, Apr 23 '14 at 09:04

How to compare 2 rows of data and show the result in 3rd rows?

1 Answers1