Sequential Numbering and Counting of Records - Sequentially Numbering Groups of Records

(Page 6 of 6 )

Another case I have run across for sequentially number records is numbering groups of records. Where each group starts numbering from 1 to N (N being the number of records in the group), and then starts over again from 1, when the next group is encountered.

For an example of what I am talking about, let’s say you have a set of order detail records for different orders, where you want to associate a line number with each order detailed record. The line number will range from 1 to N, where N is the number of order detail records per order.   The following code produces line numbers for orders in the Northwind Order Detail table.


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



               
This code is similar to the prior self join example, except this code calculates the LineNumber as part of a subquery. This way the LineNumber calculated in the subquery can be joined with the complete Order Detail record.

The above query produces the following output:


OrderID     LineNumber  ProductID   UnitPrice             Quantity Discount                 
----------- ----------- ----------- --------------------- -------- --------------- 
10248       1           11          14.0000               12       0.0
10248       2           42          9.8000                10       0.0
10248       3           72          34.8000               5        0.0
10249       1           14          18.6000               9        0.0
10249       2           51          42.4000               40       0.0
10250       1           41          7.7000                10       0.0
10250       2           51          42.4000               35       0.15000001
10250       3           65          16.8000               15       0.15000001



Conclusion
These examples represent a number of different approaches at sequentially numbering a set for records. None of these methods are perfect. But hopefully these methods will give you some ideas on how you might be able to tackle your sequential record numbering issues.

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.

Comments On: Sequential Numbering and Counting of Records

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