SQL Server RANK, ROW_NUMBER, and Partition

Reference: http://mitchelsellers.com/blogs/2008/11/01/sql-server-rank–row_number–and-partition.aspx

SQL Server RANK, ROW_NUMBER, and Partition

Posted by Mitchel on Saturday, November 01, 2008Permalink

Finally getting the opportunity to get back to SQL Server 2005 development, and coming to the conclusion that NOT all of my projects have to support SQL Server 2000 I started looking all of those "fun" new items that we all really wanted. This article is going to go through a scenario that demonstrates how to use Common Table Expressions, Rank() and Partition to get results for a very common data scenario. We will present this information with an introduction to the problem, scripts to setup the problem in a test environment, and lastly the implementation, with a review/summary at the end.

The Problem

Having an input source of data that looks similar to the below table it is easy to see how this could be real data. Take for example a data log file, where you have multiple error types that can occur with different messages.

RecordId | TypeId | ActivityDate | MessageText

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s