retrieve only the records whose value has changed

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

Transact-SQL (2008)

retrieve only the records whose value has changed

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-08 : 11:05:06

my table is LOC
here is a sample of the table
ID YEAR NUMBER
2 2003 00510
2 2004 00510
2 2005 00510
2 2006 00510
2 2007 00510
2 2008 00510
2 2009 00510
2 2010 10102
2 2011 10102

i have IDs from 1 to 100. i want to retrieve the records where the Number changes from one year to another

thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 11:15:49

select id
from loc
group by id
having count(distinct number) <> 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 11:17:19

or maybe
select *
from loc l1
join loc l2
on l1.id = l2.id
and l1.year = ld.year-1
where l1.number <> l2.number

that will give the actual changes

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2010-12-08 : 12:58:33

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources