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 add sequential row number column to a table?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor demonstrate the use of row_number() and identity() functions for sequential numbering.

-- SQL Server add row number - sql server row column - row_number sql server
-- T SQL row numbering - QUICK SYNTAX - sql row number column - select row_number
-- MSSQL Server add new sequential ProductID to table using row_number() function
-- Oracle rowid SQL Server - sql server 2005 row id - rowid equivalent sql server

SELECT NewProductID = ROW_NUMBER() OVER (ORDER BY ProductID), *
INTO #Product
FROM AdventureWorks.Production.Product
GO
SELECT * FROM #Product
/* Partial results
NewProductID      ProductID   Name
1                 1           Adjustable Race
2                 2           Bearing Ball
3                 3           BB Ball Bearing
4                 4           Headset Ball Bearings
5                 316         Blade
6                 317         LL Crankarm
7                 318         ML Crankarm
*/
-- Cleanup
DROP TABLE #Product
------------

-- T-SQL using the IDENTITY function for row numbering

DECLARE @Product TABLE(ID INT IDENTITY(1,1), ProductID int, ProductName varchar(64),

                       ListPrice money, Color varchar(32))

INSERT @Product(ProductID, ProductName, ListPrice, Color)

SELECT ProductID, Name, ListPrice, Color

FROM AdventureWorks2008.Production.Product

WHERE ListPrice > 0 AND Color is not null

ORDER BY Name

 

SELECT TOP(3) * FROM @Product ORDER BY ID

/*    ID    ProductID   ProductName       ListPrice   Color

      1     712         AWC Logo Cap      8.99        Multi

      2     952         Chain             20.24       Silver

      3     866         Classic Vest, L   63.50       Blue */

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

-- T SQL row numbering groups with partition by - row number each group - sequence
-- SQL row number each salesperson within a country with sales descending
-- MSSQL select into temporary table - create temp table
SELECT LastName + ', ' + FirstName     AS SalesPerson,
       CountryRegionName               AS Country,
       ROW_NUMBER()
         OVER(PARTITION BY CountryRegionName
              ORDER BY SalesYTD DESC)  AS 'Row Number',
       '$'+convert(varchar,SalesYTD,1) AS SalesYTD
INTO #SalesPersonRank
FROM   AdventureWorks2008.Sales.vSalesPerson
WHERE  TerritoryName IS NOT NULL
       AND SalesYTD <> 0;
GO
SELECT * FROM #SalesPersonRank ORDER BY Country, [Row Number]
/* Partial results
SalesPerson       Country           Row Number  SalesYTD
Valdez, Rachel    Germany           1           $2,241,204.04
Pak, Jae          United Kingdom    1           $5,015,682.38
Mitchell, Linda   United States     1           $5,200,475.23
Blythe, Michael   United States     2           $4,557,045.05
Carson, Jillian   United States     3           $3,857,163.63
Campbell, David   United States     4           $3,587,378.43
Ito, Shu          United States     5           $3,018,725.49
*/
DROP TABLE #SalesPersonRank
------------

-- SQL adding identity column to a table - sql sequential numbering
USE tempdb;
CREATE TABLE Department(
      Name varchar(32),
      GroupName varchar(256),
      ModifiedDate datetime
)
GO
ALTER TABLE Department ADD    DepartmentID smallint IDENTITY(1,1)
GO
-- Only one identity column per table
ALTER TABLE Department ADD    SecondIdentity smallint IDENTITY(1,1)
GO
/*
Msg 2744, Level 16, State 2, Line 1
Multiple identity columns specified for table 'Department'. Only one identity
column per table is allowed.
*/
-- SQL identity insert enabled - sql insert identity column
SET IDENTITY_INSERT Department ON
-- SQL identity insert disabled (default)
SET IDENTITY_INSERT Department OFF
-- SQL reseeding identity column - reset identity column
DBCC CHECKIDENT ("dbo.Department", RESEED, 999)
 
DROP TABLE tempdb.dbo.Department
GO
------------
 
-- MSSQL add new partition row ID to table using row_number() function
-- Microsoft SQL Server T-SQL row number over partition by order by
SELECT      RowID = ROW_NUMBER() OVER (
                  PARTITION BY p.ProductSubcategoryID ORDER BY ProductID),
      SubCategory = ps.Name,
      ProductName = p.Name,
      ProductNumber,
      Color,
      ListPrice
INTO #ProductsByCategory
FROM AdventureWorks.Production.Product p
INNER JOIN AdventureWorks.Production.ProductSubcategory ps
      ON p.ProductSubcategoryID= ps.ProductSubcategoryID
GO
SELECT * FROM #ProductsByCategory
GO
/* Partial results 

RowID SubCategory ProductName ProductNumber Color ListPrice
1 Handlebars LL Mountain Handlebars HB-M243 NULL 44.54
2 Handlebars ML Mountain Handlebars HB-M763 NULL 61.92
3 Handlebars HL Mountain Handlebars HB-M918 NULL 120.27
4 Handlebars LL Road Handlebars HB-R504 NULL 44.54
5 Handlebars ML Road Handlebars HB-R720 NULL 61.92
6 Handlebars HL Road Handlebars HB-R956 NULL 120.27
7 Handlebars LL Touring Handlebars HB-T721 NULL 46.09
8 Handlebars HL Touring Handlebars HB-T928 NULL 91.57
1 Bottom Brackets LL Bottom Bracket BB-7421 NULL 53.99
2 Bottom Brackets ML Bottom Bracket BB-8107 NULL 101.24
3 Bottom Brackets HL Bottom Bracket BB-9108 NULL 121.49
1 Brakes Rear Brakes RB-9231 Silver 106.5
2 Brakes Front Brakes FB-9873 Silver 106.5
1 Chains Chain CH-0234 Silver 20.24
1 Cranksets LL Crankset CS-4759 Black 175.49
2 Cranksets ML Crankset CS-6583 Black 256.49
3 Cranksets HL Crankset CS-9183 Black 404.99

 */
-- Cleanup
DROP TABLE #ProductsByCategory
------------

-- SQL add row number and rank number to SELECT INTO table create
-- Rank (dense ranking) high price items to low price items
SELECT   ROW_NUMBER()
           OVER(ORDER BY Name ASC)  AS ROWID,
         DENSE_RANK()
           OVER(ORDER BY ListPrice DESC) AS RANKID,
         ListPrice                       AS Price,
         *
INTO tempdb.dbo.RankedProduct
FROM     AdventureWorks2008.Production.Product
ORDER BY RANKID,  ROWID
SELECT * FROM tempdb.dbo.RankedProduct
/* Partial results
 
ROWID RANKID      Price       ProductID   Name
376   1           3578.27     750         Road-150 Red, 44
377   1           3578.27     751         Road-150 Red, 48
378   1           3578.27     752         Road-150 Red, 52
379   1           3578.27     753         Road-150 Red, 56
380   1           3578.27     749         Road-150 Red, 62
332   2           3399.99     771         Mountain-100 Silver, 38
333   2           3399.99     772         Mountain-100 Silver, 42
334   2           3399.99     773         Mountain-100 Silver, 44
335   2           3399.99     774         Mountain-100 Silver, 48
*/
------------

-- SQL add sequential row number (rowid) to table using identity(int,1,1) function
USE tempdb;
SELECT [SalesOrderID]=CONVERT(int, [SalesOrderID])
      ,[RevisionNumber]
      ,[OrderDate]
      ,[DueDate]
      ,[ShipDate]
      ,[Status]
      ,[OnlineOrderFlag]
      ,[SalesOrderNumber]
      ,[PurchaseOrderNumber]
      ,[AccountNumber]
      ,[CustomerID]
      ,[ContactID]
      ,[SalesPersonID]
      ,[TerritoryID]
      ,[BillToAddressID]
      ,[ShipToAddressID]
      ,[ShipMethodID]
      ,[CreditCardID]
      ,[CreditCardApprovalCode]
      ,[CurrencyRateID]
      ,[SubTotal]
      ,[TaxAmt]
      ,[Freight]
      ,[TotalDue]
      ,[Comment]
      ,[rowguid]
      ,[ModifiedDate]
INTO     SOH
FROM     AdventureWorks.Sales.SalesOrderHeader
ORDER BY OrderDate,
         CustomerID
GO
 
-- SQL Server T-SQl identity function for sequence generation - sequential ID
SELECT   SequentialRowNumber = identity(INT,1,1),
         *
INTO     #SOH
FROM     SOH
ORDER BY OrderDate,
         CustomerID
 
GO
 
-- The following step is normally done by sp_rename (cannot do it for temp table)
-- Microsoft SQL Server rename table example - old table renamed for future delete
-- EXEC sp_rename   SalesOrderHeader ,   zzzSalesOrderHeader
-- SQL sp rename - new table takes its place
SELECT * INTO SalesOrderHeader FROM #SOH
GO
 
SELECT   *
FROM     SalesOrderHeader
ORDER BY SequentialRowNumber
 
GO
/* Partial results
 
SequentialRowNumber     SalesOrderID
1                       43676
2                       43695
3                       43674
4                       43660
5                       43672
6                       43665
7                       43688
*/
-- Cleanup
DROP TABLE SOH
DROP TABLE #SOH
DROP TABLE tempdb.dbo.SalesOrderHeader
GO
------------

 

-- Sequence numbering subsets(partitions) with standard SQL only

-- without the use of IDENTITY or ROW_NUMBER (see LineItem)

USE Northwind;

SELECT   odet.OrderID,

         SeqNo AS LineItem,

         odet.ProductID,

         UnitPrice,

         Quantity AS Qty,

         Discount  = CONVERT(NUMERIC(3,2),Discount),

         LineTotal = CONVERT(NUMERIC(12,2),UnitPrice*Quantity*(1.0-Discount))

FROM     [Order Details] odet

         INNER JOIN

              (SELECT   count(* ) SeqNo,

                        a.OrderID,

                        a.ProductID

               FROM     [Order Details] A

                        INNER JOIN [Order Details] B

                          ON A.ProductID >= B.ProductID

                             AND A.OrderID = B.OrderID

               GROUP BY A.OrderID,

                        A.ProductID) a

           ON odet.OrderID = a.OrderID

              AND odet.ProductID = a.ProductID

WHERE    odet.OrderID < 10300

ORDER BY odet.OrderID,

         odet.ProductID,

         SeqNo

GO

/*

OrderID     LineItem    ProductID   UnitPrice   Qty   Discount    LineTotal

10248       1           11          14.00       12    0.00        168.00

10248       2           42          9.80        10    0.00        98.00

10248       3           72          34.80       5     0.00        174.00

10249       1           14          18.60       9     0.00        167.40

10249       2           51          42.40       40    0.00        1696.00

10250       1           41          7.70        10    0.00        77.00

10250       2           51          42.40       35    0.15        1261.40

10250       3           65          16.80       15    0.15        214.20

..... */
------------

Related article:

 

How to dynamically number rows in a SELECT Transact-SQL statement

Uniquely identifying each record in table like ROWID in Oracle

 

 

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