Comparing Rows in a Table

As all SQL professionals will know sqlservercentral.com is an amazing resource. In an entry titled Linking to the Previous Row the author, David McKinney, describes a simple way to compare rows within a table using the two functions introduced in SQL Server 2005: Common Table Expressions (CTE) and Rownumber().

To be honest, until I read this article, I didn't see much of a use for CTEs other than recursive queries (for more on that see: Recursive Queries in SQL Server 2005). But this example provides a tidy method for comparing, for instance, changes over time.

And if you don't already know, the Rownumber() function allows you to include, as the name suggests, a column of row numbers in your result set based on a sort order that you determine. In addition, the PARTITION BY allows you to group the results.

I will definitely be employing this