Database Code
  Home arrow Database Code arrow Code Snippets: Counting Records
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

Code Snippets: Counting Records
By: Gregory A. Larsen
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 24
    2004-03-30

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Microsoft SQL server does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. Today, we'll cover how you can number records by having an identity column, using a temporary table, altering tables, using Self Join, usiung a cursor, and numbering groups of records. See the full tutorial here.

    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_fnamefrom pubs.dbo.authors
             where au_lname 
    'G' 
           order by au_lname
    au_fname
    open authors_cursor
    fetch next from authors_cursor into 
    @name
    set 
    @0
    print 
    'recnum name'
    print 
    '------ -------------------------------'
    while 
    @@fetch_status 0
    begin
      set 
    @= @1
      
    print cast(@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.OrderIDLineNumberOD.ProductIDUnitPriceQuantityDiscount 
      from  Northwind
    .dbo.[Order DetailsOD
           join 
            
    (select count(*) LineNumber
                    a
    .OrderIDa.ProductID
                    from Northwind
    .dbo.[Order DetailsA join
                         Northwind
    .dbo.[Order Details
                         on  A
    .ProductID >= B.ProductID
                             
    and A.OrderID B.OrderID
                      group by A
    .OrderIDA.ProductIDN
              on OD
    .OrderIDN.OrderID and 
                 OD
    .ProductID N.ProductID
        where OD
    .OrderID 10251
        order by OD
    .OrderIDOD.ProductID


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More Database Code Articles
    More By Gregory A. Larsen

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Driving Business Success with Rational Process Library

    Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
    FREE! Go There Now!


    NEW! BlammoSplat: Build a community Web site of OpenLaszlo animations, Part 3: The community animation

    Learn to enable users to both rate existing animations and to combine existing animations into new snippets. This is the third in a series of three tutorials that chronicle the building of a site that enables collaborative discussion and animation building using Domino and OpenLaszlo.
    FREE! Go There Now!


    NEW! Did you say mainframe? e-kit

    Learn how you can extend modern application lifecycle management to IBM System z through the IBM Rational Software Delivery Platform (SDP). The Did you say mainframe? e-kit includes podcasts, webcasts, tutorials, white and red papers, demos, and articles designed to help ease the challenges of modernizing your enterprise. This complimentary kit for mainframe developers is a practical, how-to guide for making the most of an existing development environment, including the skills and infrastructure already in place at an established enterprise.
    FREE! Go There Now!


    NEW! Download DB2 Express-C 9.5

    Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
    FREE! Go There Now!


    NEW! Hacking 101

    Join us for this web seminar to learn how you can defend your web applications from attack. Learn about the 3 most common web application attacks, including how they occur and what can be done to prevent them. We’ll also discuss manual versus automated approaches for scanning and identifying web application vulnerabilities and how IBM Rational AppScan, an automated vulnerability scanner, can help you automate more of what you are doing manually today.
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 2: Automate builds for a real-world Tomcat project

    Learn how Rational Build Forge can extend a simple compile and package build process by adding customization and deployment capability. Go from a manual method to automating: checking for code changes; getting the latest source; compiling and packaging; customizing; copying to and restarting a deployment server; and sending e-mail notification that a new version is available.
    FREE! Go There Now!


    NEW! Integrating XML into Your Enterprise Using Data Federation

    XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
    FREE! Go There Now!


    NEW! Webcast: Accelerating Software Innovation with System z

    Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, where he will overview Rational’s new offerings and programs to help customers accelerate software innovation on System z. He will discuss how these solutions help organizations extend their core business processes toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
    FREE! Go There Now!


    NEW! Whitepaper: Achieving consistency between business process models and operational guides

    Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise.
    FREE! Go There Now!


    NEW! Whitepaper: Delivering SOA solutions: service lifecycle management

    The unprecedented scope of a service-oriented architecture (SOA) initiative brings to the forefront a number of management and governance issues that were sidestepped in the past. The key to a successful SOA implementation is managing and governing activities throughout the entire SOA delivery lifecycle by ensuring that services conform to the needs of all of the business’s stakeholders. Learn how service lifecycle management allows the business to ensure that the process by which services are defined, created, tested, deployed, optimized and retired is manageable, repeatable and auditable.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    DATABASE CODE ARTICLES

    - Deployment of the MobiLink Synchronization M...
    - MobiLink Synchronization Wizard in SQL Anywh...
    - Finding Matching Records in Data Access Pages
    - Using the AccessDataSource Control in VS 2005
    - A Closer Look at ADO.NET: The Command Object
    - A Closer Look at ADO.NET: The Connection Obj...
    - Using ADO to Communicate with the Database, ...
    - Code Snippets: Counting Records
    - Constraints In Microsoft SQL Server 2000
    - Multilingual entries into a DB and to be dis...
    - Getting A List of Tables From SQL Server
    - SQL Server Database Creator - .NET Version
    - ADO Recordset Paging
    - Two combos, one textbox example
    - Discussion & Listserv Module by Mike Eck...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 3 Hosted by Hostway
    Stay green...Green IT