Sequential Numbering and Counting of Records - Sequentially Numbering Records by Using a Temporary Table

(Page 2 of 6 )

Now you might not have designed your table to have an identity column, or even want to place one on your existing table, so another option is to insert the records you desired to have a sequence number into a temporary table. Here is some code that takes the Northwind.dbo.Employees table and copies only the Sales Representatives into a temporary table. This example uses this temporary table with a rank identity column to show a ranking of Sales Representatives by HireDate.


create table #HireDate (rank int identity, 
                    HireDate datetime,
                    LastName nvarchar(20),
                    FirstName nvarchar(20)
                    )
insert into #HireDate (HireDate, LastName, FirstName)
  select Hiredate, LastName, Firstname
    from northwind.dbo.employees
    where Title = 'Sales Representative'
    order by HireDate
Select cast(rank as char(4)) as Rank, 
       cast(hiredate as varchar(23)) as HireDate,
       LastName, 
       FirstName from #HireDate
Drop table #HireDate



The output of this example looks like this:


Rank HireDate                LastName             FirstName            
----------------------------------------------------- 
1    Apr  1 1992 12:00AM     Leverling            Janet
2    May  1 1992 12:00AM     Davolio              Nancy
3    May  3 1993 12:00AM     Peacock              Margaret
4    Oct 17 1993 12:00AM     Suyama               Michael
5    Jan  2 1994 12:00AM     King                 Robert
6    Nov 15 1994 12:00AM     Dodsworth            Anne



Next: Sequentially Numbering Records by Altering Table >>
 

More MS SQL Server Articles
More By Gregory A. Larsen

Comments On: Sequential Numbering and Counting of Records

>>> Be the FIRST to comment on this article!