Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Row Number

From Wiki

Jump to: navigation, search

Row Number(SQL Server 2000)

Since we have duplicates we can't do a running count, we will use that for DENSE_RANK Duplicates are not considered, each row has a unique number Since SQL Server 2000 doesn't have the windowing functions which are available in SQL server 2005 we have to take a different approach.

First create this table


  1. CREATE TABLE Rankings (Value Char(1))
  2.     INSERT INTO Rankings
  3.     SELECT 'A' UNION ALL
  4.     SELECT 'A' UNION ALL
  5.     SELECT 'B' UNION ALL
  6.     SELECT 'B' UNION ALL
  7.     SELECT 'B' UNION ALL
  8.     SELECT 'C' UNION ALL
  9.     SELECT 'D' UNION ALL
  10.     SELECT 'E' UNION ALL
  11.     SELECT 'F' UNION ALL
  12.     SELECT 'F' UNION ALL
  13.     SELECT 'F'


To do the equivalent of the Dense_Rank function in SQL Server 2000 we have to use a temp table with an identity column.


  1. SELECT IDENTITY(INT, 1,1) AS Rank ,Value
  2.     INTO #Ranks FROM Rankings WHERE 1=0
  3.  
  4.     INSERT INTO #Ranks
  5.     SELECT Value FROM Rankings
  6.     ORDER BY Value
  7.  
  8.     SELECT * FROM #Ranks


After we have the values in the table it is as simple as this:


Row Number(SQL Server 2005/2008)

We are starting out by creating a table


  1. CREATE TABLE Rankings (Value Char(1),id INT)
  2.     INSERT INTO Rankings
  3.     SELECT 'A',1 UNION ALL
  4.     SELECT 'A',3 UNION ALL
  5.     SELECT 'B',3 UNION ALL
  6.     SELECT 'B',4 UNION ALL
  7.     SELECT 'B',5 UNION ALL
  8.     SELECT 'C',2 UNION ALL
  9.     SELECT 'D',6 UNION ALL
  10.     SELECT 'E',6 UNION ALL
  11.     SELECT 'F',5 UNION ALL
  12.     SELECT 'F',9 UNION ALL
  13.     SELECT 'F',10


This will just add a plain vanilla row number


  1. SELECT ROW_NUMBER() OVER( ORDER BY Value ) AS 'rownumber',*
  2.     FROM Rankings


The following one is more interesting, besides the rownumber the Occurance field contains the row number count for a given value That happens when you use PARTITION with ROW_NUMBER


  1. SELECT ROW_NUMBER() OVER( ORDER BY value ) AS 'rownumber',
  2.     ROW_NUMBER() OVER(PARTITION BY value ORDER BY ID ) AS 'Occurance',*
  3.     FROM Rankings
  4.     ORDER BY 1,2


This is just ordered in alphabetical order descending


  1. SELECT ROW_NUMBER() OVER( ORDER BY Value DESC) AS 'rownumber',*
  2.     FROM Rankings


Contributed by: --SQLDenis 02:36, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Ranking, Max etc

135 Rating: 2.5/5 (34 votes cast)