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.
SyntaxThe 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
|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|