Database Code
  Home arrow Database Code arrow Code Snippets: Counting Records
Iron Speed
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  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Download TestComplete 
Windows Web Hosting
 
IBM® developerWorks 
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 / 23
    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
     
     
    Iron Speed
     
    ADVERTISEMENT

    Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

    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! Download DB2 9.5 for Linux, Unix, and Windows

    Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
    FREE! Go There Now!


    NEW! Expand the editing capabilities of OpenOffice with XSLT

    You might know that you can pull XML data into OpenOffice's spreadsheet program, Calc, but did you know that you can create a filter to make word-processing documents out of data stored as XML? This tutorial shows you how to use OpenOffice's import/export filters to open your XML data as though it's just a plain document. From there, users can edit the document much more naturally and then save it back to its native format. You can also use this feature to easily turn your documents into XML data.
    FREE! Go There Now!


    NEW! IBM Rational Systems Development e-Kit

    As systems increase in complexity, communication between systems and software teams becomes more and more difficult. Now, there’s a way to improve product quality and communication.<br />Read the “Model Driven Systems Development” white paper to see how. Also included in this kit are more educational white papers, customer examples, tutorials, informative Webcasts, and best practices for designing, building and managing systems.<br />
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment

    Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
    FREE! Go There Now!


    NEW! Spot defects early with Continuous Integration

    Continuous Integration (or CI) is a process that consists of continuously compiling, testing, inspecting, and deploying source code. In many Continuous Integration environments, this means running a new build anytime code within a source code management repository changes. The benefit of CI is simple: assembling software often greatly increases the likelihood that you will spot defects early, when they still are relatively manageable. In this tutorial, a companion to his series In pursuit of code quality, Andrew Glover introduces the fundamental aspects of Continuous Integration and steps you through how to set up a CI process using best-of-breed open source technologies.
    FREE! Go There Now!


    NEW! Trial download: IBM Rational Manual Tester V7.0.1

    Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
    FREE! Go There Now!


    NEW! Trial download: IBM Rational Tester for SOA Quality V7.0.1

    Get a free trial download of the latest version of IBM Rational Tester for SOA Quality V7.0.1, a functional and regression testing tool that enables the creation, comprehension, modification and execution of testing GUI-less Web services.
    FREE! Go There Now!


    NEW! Using IBM Rational Tester for SOA Quality: Using IBM Rational Tester for SOA Quality with IBM WebSphere MQ Version 6.0

    Learn how IBM Rational Tester for SOA Quality addresses IBM WebSphere MQ with Web services. You get hands-on experience in creating a test, handling the WebSphere MQ series protocol, configuring the test, and then replaying it.
    FREE! Go There Now!


    NEW! Web development with Eclipse Europa, Part 1: The Java EE for Eclipse

    It's a good time to be a Web developer. You've never had more choices in terms of technologies. There are so many great open source Web servers, databases, programming languages, and development frameworks. No matter what combination of technologies you prefer to work with, there is an integrated development environment (IDE) that can increase your productivity: Eclipse. In this tutorial, Part 1 of a three-part "Web development with Eclipse Europa" series on how to use Eclipse for Web development with Java technology, PHP, and Ruby, we'll see how the latest release of Eclipse -- Europa -- can be used to rapidly develop Java Web applications. We'll use Java Platform, Enterprise Edition 5 (Java EE) for Eclipse to build a Web application for tracking and calculating baseball statistics.
    FREE! Go There Now!


    NEW! Web development with Eclipse Europa, Part 2: The Java EE for Eclipse

    No matter what combination of technologies you prefer to work with as a Web developer, Eclipse is a single integrated development environment (IDE) that can increase your productivity. In Part 2, we'll see how easy it is to develop PHP applications using a different set of Eclipse plug-ins, collectively known as the PHP Development Toolkit (PDT.)
    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...

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 6 hosted by Hostway