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!

more ▼

asked Mar 09, 2011 at 04:37 PM in Default

avatar image

13 1 1 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
         ROW_NUMBER() over (order by DateOfEntry) as RowNum
     records r1
     LEFT OUTER JOIN records r2 on r1.RowNum = r2.RowNum + 1
     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.

more ▼

answered Mar 09, 2011 at 04:57 PM

avatar image

Kevin Feasel
6.2k 4 8 15

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.

Mar 10, 2011 at 09:58 AM bugs2bugs

The "with" use there is called a Common Table Expression. They were added to SQL Server 2005 and are extremely helpful in a number of circumstances.

Mar 10, 2011 at 10:31 AM Kevin Feasel
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Feb 15 at 08:52 AM

avatar image


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

Feb 19 at 01:30 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 09, 2011 at 04:37 PM

Seen: 12925 times

Last Updated: Feb 19 at 01:30 PM

Copyright 2018 Redgate Software. Privacy Policy