November 6, 2002 at 4:20 pm
I have a table with 3 rows. How can I compare the data in row1 with the data in row2 to see what the differences are. It this case it may only be one column or could be all columns. Thanks for any suggestions on this.
November 6, 2002 at 4:41 pm
I think you can do it by doing the self join to the table.
November 6, 2002 at 5:24 pm
Depends on how you are comparing the rows if you need to compare the row items only to those in the same row you can use a case statement to give you an idea
Ex.
SELECT idcol, (col12 + col13 + col23) as numcoldif
(
SELECT idcol,
CASE WHEN col1 != col2 THEN 1 else 0 END as col12,
CASE WHEN col1 != col3 THEN 1 else 0 END as col13,
CASE WHEN col2 != col3 THEN 1 else 0 END as col23
FROM mytble
) as SubTable
ORDER BY
numcoldif
in the example I assumed you have a column to identity the rows. By doing it this way you can see from least number of differences to most what row has how many differences.
Otherwise a self join is your best bet.
Edited by - antares686 on 11/06/2002 5:24:56 PM
November 7, 2002 at 2:40 am
Have you checked out the BINARY_CHECKSUM() function?
November 7, 2002 at 4:26 am
quote:
Have you checked out the BINARY_CHECKSUM() function?
Just curious, what would I do with it other than what I stated in my post. It is cood though in that it does a case sensitive compare if added to my example. Is there another thought as I had never looked at this myself.
November 7, 2002 at 6:55 am
When comapring data you also make sure you are handling NULLS correctly. i.e. NULL = NULL is not true when ANSI_NULLS is off. either turn it on or use ISNULL and a value that won't be used. i.e
ISNULL(table1.FKCol1,-1)=ISNULL(table2.FKCol1,-1)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply