question

bugs2bugs avatar image
bugs2bugs asked

query to find when a value changes from one row to the next?

My data looks like this: Date Amount Jan 1 100 Feb 1 100 Mar 1 150 Apr 1 150 May 1 100 Jun 1 100 I need a query that just shows the date/amount when the amount changes: Date Amount Jan 1 100 Mar 1 150 May 1 100 Initially I was just using group by and finding the minimum date, but the amount could change back to a previous value, so it could be a little tricky. Thanks!
query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
Here's a way to do it using a windowing function (ROW_NUMBER in this case): create table #DataTable ( DateOfEntry datetime, Amount int ) insert into #DataTable values('2011-01-01', 100); insert into #DataTable values('2011-02-01', 100); insert into #DataTable values('2011-03-01', 150); insert into #DataTable values('2011-04-01', 150); insert into #DataTable values('2011-05-01', 100); insert into #DataTable values('2011-06-01', 200); insert into #DataTable values('2011-07-01', 600); insert into #DataTable values('2011-08-01', 600); with records as ( select DateOfEntry, Amount, ROW_NUMBER() over (order by DateOfEntry) as RowNum from #DataTable ) select r1.* from records r1 LEFT OUTER JOIN records r2 on r1.RowNum = r2.RowNum + 1 where r2.RowNum IS NULL OR r2.Amount r1.Amount; drop table #DataTable; In this example, we're getting row numbers based on the date. ROW_NUMBER will increment with no repeats and no gaps, so you can join the CTE (in 2005; if you're using 2000, turn records into a subquery) on the row number, as you want to see if the current record differs from the previous record. If it does, mark the change. Also, because the first record won't join to anything, include it as well--you change from nothing to something.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bugs2bugs avatar image bugs2bugs commented ·
Thanks, that worked. I was thinking about something involving rownum but couldn't put this together. I wasn't familiar with "with" either, so that's a new addition to my SQL repertoire.
0 Likes 0 ·
Kevin Feasel avatar image Kevin Feasel commented ·
The "with" use there is called a [Common Table Expression]( http://msdn.microsoft.com/en-us/library/ms190766.aspx). They were added to SQL Server 2005 and are extremely helpful in a number of circumstances.
0 Likes 0 ·
saurabhvns avatar image
saurabhvns answered
Can't we have something without using ROW_NUMBER(), actually I am looking answer of the same question in a single line query to implement it in amazon kinesis
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
you could do it in t-sql with the lag function; but for kinesis? No idea. Perhaps it would be better to ask as a separate question...?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.