Comparing two rows in a Table

Hi Gurus,
Is there any automated way to compare the contents of TWO rows in the same
table of SQL Server ?
Thanks in advance.
Regards,
YogWhat do mean by "Automated"? There is certainly a way, just write a query
that returns a boolean 1/0 based on whether the row column values are the
same or different... How to "Automate" it, depends on what "event" you want
to "trigger", (cause ) it to run... If yo want it to run automatically on
some time interval, use SQLAgent...
If you want it to run whenever someone inserts, updates, or deletes a record
from the table, then use a trugger.
"Yog" wrote:

> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the same
> table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog|||What result do you want from the comparison? Do you mean you want to
compart two particular rows? In that case you can use a self-join on
the columns you want to compare.
Maybe you just want to find duplicates, in which case:
SELECT col1, col2, col3, ...
FROM YourTable
GROUP BY col1, col2, col3, ...
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||Yog wrote:
> Hi Gurus,
> Is there any automated way to compare the contents of TWO rows in the
> same table of SQL Server ?
> Thanks in advance.
> Regards,
> Yog
You can use the CHECKSUM() function as in:
Create table #checktest(col1 int, col2 int, col3 int)
insert into #checktest values (1, 2, 2)
insert into #checktest values (2, 2, 2)
insert into #checktest values (3, 2, 3)
Select CHECKSUM(col2, col3) as "ID 1"
From #checktest
where col1 = 1
UNION ALL
Select CHECKSUM(col2, col3) as "ID 2"
From #checktest
where col1 = 2
UNION ALL
Select CHECKSUM(col2, col3) as "ID 3"
From #checktest
where col1 = 3
drop table #checktest
David Gugick
Imceda Software
www.imceda.com|||To precisely put it , the content of the problem is as below
Objective :- Compare TWO rows contents for few selected Columns. If they are
different then show those columns with the contents.
ENV:- SQL Server 2000 , T-SQL, SQL Server Tools (No other programming
language or front end)
"CBretana" wrote:
> What do mean by "Automated"? There is certainly a way, just write a quer
y
> that returns a boolean 1/0 based on whether the row column values are the
> same or different... How to "Automate" it, depends on what "event" you wa
nt
> to "trigger", (cause ) it to run... If yo want it to run automatically on
> some time interval, use SQLAgent...
> If you want it to run whenever someone inserts, updates, or deletes a reco
rd
> from the table, then use a trugger.
> "Yog" wrote:
>|||SELECT A.col1, A.col2, ...
FROM YourTable AS A,
YourTable AS B
WHERE A.x = ?
AND B.x = ? /* Specify the two rows to be compared */
AND
(A.col1<>B.col1
OR A.col1<>B.col1
OR ...)
David Portas
SQL Server MVP
--|||Hi David,
Many thanks for your POST.
Your answer provided me HINT to achieve my goal of comparing two rows for my
requirement.
Regards,
Yog
"David Portas" wrote:

> SELECT A.col1, A.col2, ...
> FROM YourTable AS A,
> YourTable AS B
> WHERE A.x = ?
> AND B.x = ? /* Specify the two rows to be compared */
> AND
> (A.col1<>B.col1
> OR A.col1<>B.col1
> OR ...)
> --
> David Portas
> SQL Server MVP
> --
>