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

Find ramblings

Friday, August 20, 2010

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

SQL Saturday 53, takes place in 43 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 DENSE_RANK function is the third of four windowing functions introduced in SQL Server 2005. DENSE_RANK is nearly identical to RANK except that in the case of a tie or duplicate values, DENSE_RANK will leave no gaps in numbering.


The syntax for DENSE_RANK is identical to RANK(). It's DENSE_RANK() OVER (ORDER BY _) , parenthesis required. Using the upcoming SQL Saturday calendar, I added a call to DENSE_RANK() based on event date to introduce a rank column.
-- This query demonstrates the use of the DENSE_RANK function
-- Notice the ranks for rows with same key (event_date) are 
-- ranked equally and the subsequent rank number leaves
-- no gaps in the numbers.
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'
,   DENSE_RANK() OVER (ORDER BY SS.event_date ASC) AS zee_dense_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 20103
2010-10-02SQLSaturday #48Columbia 20104
2010-10-02SQLSaturday #53Kansas City 20104
2010-10-16SQLSaturday #49Orlando 20105
2010-10-23SQLSaturday #56Dallas (BI Edition) 20106
2010-10-23SQLSaturday #54Salt Lake City 20106
2010-10-29SQLSaturday #58Minnesota 20107
2010-11-20SQLSaturday #59New York City 20108
2011-01-22SQLSaturday #45Louisville 20119
2011-01-29SQLSaturday #57Houston 201110
2011-02-05SQLSaturday #47Phoenix 201111

What to notice

I created a new column called "zee_dense_rank" with the invocation of the DENSE_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 3rd versus 5th if RANK had been used.


SQL Server 2005+

No comments: