A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, August 19, 2010

SQL Server 2005/2008 what's new, part 4

SQL Saturday 53, takes place in 44 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.


The RANK function is the second of four windowing functions introduced in SQL Server 2005. RANK is nearly identical to ROW_NUMBER except that in the case of a tie or duplicate values, ROW_NUMBER will select one as coming first. RANK is a New Age, granola loving function that says "you're all winners in my book" and gives all the matches the same value. RANK keeps track of how many ties for Nth place occur and adjusts the next number accordingly.


The syntax for RANK is identical to ROW_NUMBER(). It's RANK() OVER (ORDER BY _) , parenthesis required. Using the upcoming SQL Saturday calendar, I added a call to RANK() based on event date to introduce a rank column.
-- This query demonstrates the use of the RANK function
-- Notice the ranks for rows with same key (event_date) are 
-- ranked equally and the subsequent rank number behaves
-- like an identity insert that was rolled back, i.e. gaps
-- in the meaningless identifiers that someone (QA) will question
WITH SQL_SATURDAY (event_date, event_name, event_location) AS
SELECT CAST('Aug 21, 2010' as datetime), 'SQLSaturday #51', 'Nashville 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #46', 'Raleigh 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #50', 'East Iowa 2010'
UNION ALL SELECT 'Sep 18, 2010', 'SQLSaturday #55', 'San Diego 2010'
UNION ALL SELECT 'Sep 25, 2010', 'SQLSaturday #52', 'Colorado 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #53', 'Kansas City 2010'
UNION ALL SELECT 'Oct 2, 2010', 'SQLSaturday #48', 'Columbia 2010'
UNION ALL SELECT 'Oct 16, 2010', 'SQLSaturday #49', 'Orlando 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #54', 'Salt Lake City 2010'
UNION ALL SELECT 'Oct 23, 2010', 'SQLSaturday #56', 'Dallas (BI Edition) 2010'
UNION ALL SELECT 'Oct 29, 2010', 'SQLSaturday #58', 'Minnesota 2010'
UNION ALL SELECT 'Nov 20, 2010', 'SQLSaturday #59', 'New York City 2010'
UNION ALL SELECT 'Jan 22, 2011', 'SQLSaturday #45', 'Louisville 2011'
UNION ALL SELECT 'Jan 29, 2011', 'SQLSaturday #57', 'Houston 2011'
UNION ALL SELECT 'Feb 5, 2011', 'SQLSaturday #47', 'Phoenix 2011'
,   RANK() OVER (ORDER BY SS.event_date ASC) AS zee_rank
2010-08-21SQLSaturday #51Nashville 20101
2010-09-18SQLSaturday #50East Iowa 20102
2010-09-18SQLSaturday #55San Diego 20102
2010-09-18SQLSaturday #46Raleigh 20102
2010-09-25SQLSaturday #52Colorado 20105
2010-10-02SQLSaturday #48Columbia 20106
2010-10-02SQLSaturday #53Kansas City 20106
2010-10-16SQLSaturday #49Orlando 20108
2010-10-23SQLSaturday #56Dallas (BI Edition) 20109
2010-10-23SQLSaturday #54Salt Lake City 20109
2010-10-29SQLSaturday #58Minnesota 201011
2010-11-20SQLSaturday #59New York City 201012
2011-01-22SQLSaturday #45Louisville 201113
2011-01-29SQLSaturday #57Houston 201114
2011-02-05SQLSaturday #47Phoenix 201115

What to notice

I created a new column called "zee_rank" with the invocation of the RANK function. Because I ordered it by dates, and we have 3 events all on 2010-09-18, the rank starts at 1 for Nashville represent! East Iowa, San Diego and Raleigh battled it out for supremacy on 2010-09-18 and it was a three way tie at 2 which resulted in Colorado being ranked 5th.


SQL Server 2005+

No comments: