select only changes compared to previous row

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)

select only changes compared to previous row

Author

Topic

3DBug
Starting Member

2 Posts

Posted - 2012-12-06 : 04:58:41

Hi there :)
Perhaps someone can help me with this...
SQL Server 2008
Have Table Data1..

Date_TimeA, Value1, Value2,....Value225..
2012-10-04 14:34:16.000 33,66,....1815
2012-10-04 14:35:17.000 11,23,....1815
2012-10-04 14:32:16.000 62,64,....1815
2012-10-04 14:31:19.000 33,66,....1815
2012-10-04 14:45:16.000 11,23,....9999
2012-10-04 14:52:15.000 62,64,....1815
2012-10-04 12:35:16.000 11,23,....9999

many entrys every minute one (about 2 years of data), but not allways in order of Date_Time..
with this i make querys between Date_Times ordered by Date_Time..
(using this for Charts-trending)

now i would like to see only the rows where the Value225 changed from the previous row
2012-10-04 12:35:16.000 11,23,....9999
2012-10-04 14:31:19.000 33,66,....1815 ****
2012-10-04 14:32:16.000 62,64,....1815
2012-10-04 14:34:16.000 33,66,....1815
2012-10-04 14:35:17.000 11,23,....1815
2012-10-04 14:45:16.000 11,23,....9999 ****
2012-10-04 14:52:15.000 62,64,....1815

the output doesn't need to include Value1,Value2 etc.. just need the Date_Time and ValueV225
think.. it could work somwehow to build a select with function ROW_NUMBER..
but problem ist between my ears ( how to.. do it in a fast way )

Perhaps someone can help me .. thx

please excuse my rusty english ;)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-06 : 05:25:14

;with cte as
(
select Date_TimeA, Value225, seq = row_number() over (order by Date_TimeA) from Data1
)
select c1.Date_TimeA, c1.Value225, case when c1.Value225 <> c2.Value225 or c2.Value225 is null then '****' else '' end)
from cte c1 left join cte c2
on c1.seq = c2.seq+1

if you only want the changed rows
select c1.Date_TimeA, c1.Value225
from cte c1 left join cte c2
on c1.seq = c2.seq+1
where c1.Value225 <> c2.Value225 or c2.Value225 is null

==========================================
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.

3DBug
Starting Member

2 Posts

Posted - 2012-12-06 : 06:14:15

thx .. that's what i was looking for :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources