Code Snippets: Counting Records

Sequentially Numbering Records by Having an Identity Column


SET NOCOUNT ON
CREATE TABLE SEQ_NUMBER_EXAMPLE (
   RECORD_NUMBER INT IDENTITY (1,1),
   DESCRIPTION VARCHAR(40))
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIRST RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('SECOND RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('THIRD RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FOURTH RECORD')
INSERT INTO SEQ_NUMBER_EXAMPLE VALUES('FIFTH RECORD')
SELECT * FROM SEQ_NUMBER_EXAMPLE
DROP TABLE SEQ_NUMBER_EXAMPLE



Sequentially Numbering Records by Using a Temporary Table


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



Sequentially Numbering Records by Altering Table


set nocount on
alter table pubs.dbo.titles
 add rownum int identity(1,1)
go
select rownum, title from pubs.dbo.titles
   where rownum < 6
   order by rownum
go
alter table pubs.dbo.titles
drop column rownum



Sequentially Numbering Records by Using a Self Join


SELECT count(*) RecNum,
       a.LastName
     FROM Northwind.dbo.Employees a join
          Northwind.dbo.Employees b
          on a.LastName >= b.LastName
     group by a.LastName
     order by a.LastName



Sequentially Number Records by Using a Cursor


declare @i int
declare @name varchar(200)
declare authors_cursor cursor
   for select rtrim(au_lname) + ', ' + rtrim(au_fname) from pubs.dbo.authors
         where au_lname < 'G' 
       order by au_lname, au_fname
open authors_cursor
fetch next from authors_cursor into @name
set @i = 0
print 'recnum name'
print '------ -------------------------------'
while @@fetch_status = 0
begin
  set @i = @i + 1
  print cast(@i as char(7)) + rtrim(@name)
  fetch next from authors_cursor into @name
end
close authors_cursor
deallocate authors_cursor



Sequentially Numbering Groups of Records


select OD.OrderID, LineNumber, OD.ProductID, UnitPrice, Quantity, Discount 
  from  Northwind.dbo.[Order Details] OD
       join 
        (select count(*) LineNumber, 
                a.OrderID, a.ProductID
                from Northwind.dbo.[Order Details] A join
                     Northwind.dbo.[Order Details] B 
                     on  A.ProductID >= B.ProductID
                         and A.OrderID = B.OrderID
                  group by A.OrderID, A.ProductID) N
          on OD.OrderID= N.OrderID and 
             OD.ProductID = N.ProductID
    where OD.OrderID < 10251
    order by OD.OrderID, OD.ProductID