How to compare each row of the column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to compare each row of the column

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-04-06 : 12:12:27

Hello:

I need to compare the third column: Month each row. For example: the second row of Month at original table is 2,
The comparable table is 3. The twelfth column of Month at original table is 12. The comparable table is 13.

original table


Name	NameID  Month    Base    Factor
JJ	2222	1	1	0
JJ	2222	2	2	0
JJ	2222	3	3	0
JJ	2222	4	4	0
JJ	2222	5	5	0
JJ	2222	6	6	0
JJ	2222	7	7	0
JJ	2222	8	8	0
JJ	2222	9	9	0
JJ	2222	10	10	0
JJ	2222	11	11	0
JJ	2222	12	12	0

Comparable table


Name	NameID  Month    Base    Factor
JJ	2222	1	1	0
JJ	2222	3	3	0
JJ	2222	4	4	0
JJ	2222	5	5	0
JJ	2222	6	6	0
JJ	2222	7	7	0
JJ	2222	8	8	0
JJ	2222	9	9	0
JJ	2222	10	10	0
JJ	2222	11	11	0
JJ	2222	12	12	0
JJ	2222	13	12	0

If there is the difference, give the message: it is different.

Is any idea how to accomplish this?

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:28:25

you need to determine on what basis you need to order for the comparison. There's no concept of order in sql table unless you specify it by mean of order by

anyways i'm assuming you need comparison based on sequence of Month column

if yes use below


SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY [Month]) AS Seq,* FROM original)o
LEFT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY NameID ORDER BY [Month]) AS Seq,* FROM Comparable)c
ON c.NameID  = o.NameID  
AND c.Seq = o.Seq
AND c.[Month] <> o.[Month]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-04-10 : 11:22:08

Thank you very much for the reply.

When I tried this query, I got the error:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'ON'.

at ON c.NameID = o.NameID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-10 : 16:09:41

i'm not getting that error. please post your used query if its different from mine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources