COMPARISON BETWEEN ROWS - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

COMPARISON BETWEEN ROWS

Author

Topic

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-09-17 : 22:41:11

Hi,

I would like to know if there is any way for me to compare rows in a table.

create table TEST
(
[code] [int],
[name] [nvarchar] (100),
[address] [nvarchar] (200),
[city] [nvarchar] (100)
)

insert into TEST
select 1, 'ann', 'fifth ave', 'newyork'
union all
select 2, 'ann', 'fifth ave', 'spore'

based on the data, I would like to have result:

difference
----------
code
city

thanks for your kind attention.

regards,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-17 : 23:02:41

Will this do ?


create table TEST
(
  [code]    [int],
  [name]    [nvarchar] (100),
  [address] [nvarchar] (200),
  [city]    [nvarchar] (100)
)

insert into TEST
select 1, 'ann', 'fifth ave', 'newyork' union all
select 2, 'ann', 'fifth ave', 'spore'   union all
select 3, 'bob', 'sixth ave', 'spore'	union all
select 4, 'bob', 'sixth ave', 'newyork'

select 	*
from	TEST t1 inner join TEST t2
	on	t1.name	= t2.name
where	t1.code		<> t2.code
or	t1.address	<> t2.address
or	t1.city		<> t2.city

drop table TEST

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-17 : 23:07:51

or this ?


create table TEST
(
	[code] 		[int],
	[name] 		[nvarchar] (100),
	[address] 	[nvarchar] (200),
	[city] 		[nvarchar] (100)
)

insert into TEST
select 1, 'ann', 'fifth ave', 'newyork' union all
select 2, 'ann', 'fifth ave', 'spore'   union all
select 3, 'bob', 'sixth ave', 'spore'	union all
select 4, 'bob', 'sixth aves', 'spore'

select 	t1.name, 'code' as diff
from	TEST t1 inner join TEST t2
	on	t1.name	= t2.name
where	t1.code		<> t2.code
group by t1.name

union all

select 	t1.name, 'address'
from	TEST t1 inner join TEST t2
	on	t1.name	= t2.name
where	t1.address		<> t2.address
group by t1.name

union all

select 	t1.name, 'city' 
from	TEST t1 inner join TEST t2
	on	t1.name	= t2.name
where	t1.city		<> t2.city
group by t1.name
/*
name   diff                                                                                                  
------ ------- 
ann    code
bob    code
bob    address
ann    city

(4 row(s) affected)
*/
drop table TEST

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-09-18 : 00:00:44

Hi KHTan,

Thanks for your replies.
For your information, my table has more than 50columns, this solution will cost me more time and I also need to modify the codes if the structure of table is changed.

Please understand, I'm not saying your codes are not good, it's simply because it doesnt suit my case.

Do you have more dynamic solution?

regards,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-18 : 00:19:34

Will the 1st query work for you ? or You required output as in the 2nd query i posted ?

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-09-18 : 01:39:47

Hi KHTan,

The second query is closer to what I want.
Basically, I only need to know which column(s) were changed.

Thanks.

regards,
erwine

... sql is fun...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-18 : 01:53:40

"my table has more than 50columns, this solution will cost me more time and I also need to modify the codes if the structure of table is changed. "

You can consider using Dynamic SQL to do it. Use INFORMATION_SCHEMA.COLUMNS to obtain the column names.

For more information on Dynamic SQL, refer to
http://www.sommarskog.se/dynamic_sql.html

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-09-18 : 02:02:51

Yes.
Thanks for the link.

regards,
erwine

... sql is fun...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-18 : 11:33:29

Why do you want this type of comparison?
Refer this also

http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

Madhivanan

Failing to plan is Planning to fail

erwine
Yak Posting Veteran

69 Posts

Posted - 2006-09-20 : 06:06:38

"Why do you want this type of comparison?"

I need it because it will give me the information of which column(s)(data) that is last updated. I'm not comparing a table, but I actually want to compare 2 rows(the last and the second last).

Have you done this before, Madhivanan?
Thanks for your attention.

regards,
erwine

... sql is fun...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources