Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I ran across a post where someone that was trying to access the previous value in a table for some criteria. This is a common issue, and one that’s very easily solved in SQL Server 2012+ with the windowing functions.
However, what about in SQL Server 2008 R2-?
NOTE: I’m solving this quickly, the way many people do, but this is an inefficient solution. I’ll show that in another post. However, I’m showing how you can describe and solve a problem here. If you need to solve this, look for a temp table solution (or find a later post from me).
Setup
It’s pretty easy. Let’s get some data together. I’ll use a big sample since that’s easier to see the differences.
CREATE TABLE MyID
( myid INT
, myvalue INT
);
GO
INSERT MyID
VALUES (1, 10 ),
(1, 20 ),
(2, 400),
(2, 500),
(2, 600),
(3, 8000),
(3, 9000),
(3, 10000),
(3, 11000);
Now, what I want is something that returns the previous row, assuming we’re ordering by the ID and value. If there is no previous value, let’s return a zero. Essentially what we want is something like this:
select MyID , MyValue , MyPrevValue = ISNULL( x, 0) from …
That’s the pseudocode. Obviously I need to fill in blanks. However, let’s build a test. Why? Well, I can then see my result data, and I can re-run the test over and over as I experiment with the query. It’s not hard, I promise.
EXEC tsqlt.NewTestClass
@ClassName = N'WindowTests';
go
CREATE PROCEDURE [WindowTests].[test check the previous row value for MyID]
AS
BEGIN
-- assemble
CREATE TABLE #expected (id INT, myvalue INT, PrevValue int) INSERT #expected
VALUES (1, 10 , 0 ),
(1, 20 , 10 ),
(2, 400 , 20 ),
(2, 500 , 400),
(2, 600 , 500),
(3, 8000 , 600),
(3, 9000 , 8000),
(3, 10000 , 9000),
(3, 11000 , 10000) SELECT *
INTO #actual
FROM #expected AS e
WHERE 1 = 0 -- act
INSERT #actual
EXEC dummyquery;
-- assert
EXEC tsqlt.AssertEqualsTable
@Expected = N'#expected'
, @Actual = N'#actual'
, @FailMsg = N'Incorrect query' END
If you examine the test, you’ll see that I create a table, insert the results I expect, and then call some procedure. I compare the results of the procedure with the table I built.
That’s it. A simple test, but I’ll let the computer compare the result sets rather than trusting my eyes.
Last thing, I’ll build my dummy procedure, which can look like this:
CREATE PROCEDURE dummyquery
-- alter procedure dummyquery
AS
BEGIN select MyID , MyValue , PrevValue = MyValue from MyID
END
Now I have the outline of what I need. If I run the test now, I’ll get this:
The test output tells me it has failed, the values in the #expected table (with a <), and the values from my query in the #actual table (with a >).
Now I can debug and work on this.
Solving the Problem
First, I want to order the data and get a number that counts the order. The ROW_NUMBER function does this, which is available in SQL Server 2005+. I won’t go into SQL 2000- solutions because, well they’re more complex and there should be very few SQL 2000 instances left coming up with new problems.
I can do this with this code:
Note that I have a sequential counter that lets me order every row with an index. Now, I can access the previous row, since I know the MyKey value will be one less than the current row.
With this in mind, let’s turn this into a CTE (removing the previous value). Outside of the CTE, I’m going to self-join the CTE to itself. I’ll use a LEFT JOIN since not every row will have a previous row. In fact, the first row won’t.
The join condition, which you can play with, will be on the outer table’s ID being one less than the first table’s key. You could reverse the math as well, but that’s up to you.
One last issue. Add an ISNULL to the previous value to return a 0 if there is no match. Now, let’s run the test.
SQLNewBlogger
This was a slightly longer post, where I tried to explain how I setup the problem and solved it. I included a test, which didn’t add much coding time. In fact, the writing took far longer than the coding itself.
This is the type of problem I’d encourage you to solve on your blog. If you want to repeat this, look for a solution with temp tables, as the CTE incurs a lot of reads. This isn’t really what you’d like to do in production code.
