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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply