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.

RANK()

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.

Syntax

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'
)
SELECT
    SS.* 
,   RANK() OVER (ORDER BY SS.event_date ASC) AS zee_rank
FROM 
    SQL_SATURDAY SS

event_date	event_name	event_location	zee_rank
2010-08-21	SQLSaturday #51	Nashville 2010	1
2010-09-18	SQLSaturday #50	East Iowa 2010	2
2010-09-18	SQLSaturday #55	San Diego 2010	2
2010-09-18	SQLSaturday #46	Raleigh 2010	2
2010-09-25	SQLSaturday #52	Colorado 2010	5
2010-10-02	SQLSaturday #48	Columbia 2010	6
2010-10-02	SQLSaturday #53	Kansas City 2010	6
2010-10-16	SQLSaturday #49	Orlando 2010	8
2010-10-23	SQLSaturday #56	Dallas (BI Edition) 2010	9
2010-10-23	SQLSaturday #54	Salt Lake City 2010	9
2010-10-29	SQLSaturday #58	Minnesota 2010	11
2010-11-20	SQLSaturday #59	New York City 2010	12
2011-01-22	SQLSaturday #45	Louisville 2011	13
2011-01-29	SQLSaturday #57	Houston 2011	14
2011-02-05	SQLSaturday #47	Phoenix 2011	15

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.

Availability

SQL Server 2005+