DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply all the RANKING functions?

Execute the following Microsoft T-SQL example query scripts in SQL Server Management Studio Query Editor to compare the ROW_NUMBER, RANK, DENSE_RANK and NTILE ranking functions.

-- SQL RANK function - Quick Syntax - sql rank over partition

SELECT

      CustomerID,

      convert(date,OrderDate) as OrderDate,

      RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RankNo

FROM AdventureWorks2008.Sales.SalesOrderHeader

ORDER BY CustomerID, RankNo

/* Partial results

CustomerID  OrderDate   RankNo

11035       2004-05-18  1

11035       2004-03-13  2

11035       2001-08-09  3

11036       2003-07-26  1

11037       2003-07-24  1

*/

------------

-- SQL RANK function usage - select most recent order(s) for each customer

SELECT * FROM

(SELECT

      CustomerID,

      convert(date,OrderDate) as OrderDate,

      RANK() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RankNo

FROM AdventureWorks2008.Sales.SalesOrderHeader ) x

WHERE RankNo = 1

ORDER BY CustomerID

------------

------------

-- Microsoft T-SQL ranking functions comparison

------------

-- MSSQL row_number (row number sequence) - NTILE

-- MSSQL rank and dense_rank functions - over order by clause

-- Data is rounded down to the thousands for ranking

USE AdventureWorks;

SELECT  

         c.AccountNumber                  AS CustAccount,

         FLOOR(h.SubTotal /1000)          AS [SubTotal (Thousands $)],

         ROW_NUMBER()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS RowNumber,

         RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS Rank,

         DENSE_RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS DenseRank,

         NTILE(5)

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS NTile

FROM     Sales.Customer c

         INNER JOIN Sales.SalesOrderHeader h

           ON c.CustomerID = h.CustomerID

         INNER JOIN Sales.SalesTerritory t

           ON h.TerritoryID = t.TerritoryID

WHERE    t.Name = 'Germany'

  AND    YEAR(OrderDate) = 2004

  AND    SubTotal >= 4000.0

ORDER BY RowNumber;

GO

/* Results

CustAccount SubTotal (Thousands $) RowNumber Rank DenseRank NTile
AW00000230 100 1 1 1 1
AW00000230 88 2 2 2 1
AW00000302 77 3 3 3 1
AW00000320 68 4 4 4 1
AW00000536 68 5 4 4 1
AW00000536 64 6 6 5 1
AW00000266 58 7 7 6 1
AW00000302 44 8 8 7 2
AW00000687 43 9 9 8 2
AW00000482 36 10 10 9 2
AW00000176 36 11 10 9 2
AW00000464 35 12 12 10 2
AW00000320 35 13 12 10 2
AW00000176 34 14 14 11 2
AW00000464 34 15 14 11 3
AW00000266 34 16 14 11 3
AW00000482 31 17 17 12 3
AW00000687 29 18 18 13 3
AW00000410 16 19 19 14 3
AW00000428 13 20 20 15 3
AW00000572 13 21 20 15 4
AW00000410 12 22 22 16 4
AW00000086 9 23 23 17 4
AW00000086 9 24 23 17 4
AW00000068 7 25 25 18 4
AW00000068 7 26 25 18 4
AW00000428 7 27 25 18 5
AW00000284 7 28 25 18 5
AW00000284 7 29 25 18 5
AW00000104 4 30 30 19 5
AW00000068 4 31 30 19 5
AW00000068 4 32 30 19 5

*/

------------

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE