From: esquel@sommarskog.se   
      
   Guy Dreger (isgdre@HotMail.com) writes:   
   > Nice, Thanks for the info.   
   >   
   > A couple of things though   
   >   
   > row_number()'s help says it starts at 1.  I take it I would have to add   
   > max(LineNumber) from the OrderDetails some how. I'd work on it but the   
   > next ones a killer.   
      
   Yes.   
      
   > I unfortunately have no control over the SQL Server version.  This is an   
   > add on to an existing application that supports SQL Server 2000 and 2005   
   > and is currently working on 2008 compatibility.   
   >   
   > So. the 2005 requirement is a problem.  Any other ideas?   
      
   Yes, next time please state which version of SQL Server you are using.   
      
   On SQL 2000 you can use a correlated subquery with COUNT(*):   
      
      SELECT a.OrderID, a.ProductID,   
             lineno = (SELECT COUNT(*)   
                       FROM   tbl b   
                       WHERE  b.OrderID = a.OrderID   
                         AND  b.ProductID <= a.Productid)   
      FROM   tbl a   
      
   However, for large datasets this will perform outright poor. In that case   
   it may be better better to bounce the data over a temp table with an   
   IDENTITY column, and the use the IDENTITY column to build the line   
   numbers.   
      
      
   Tip: try to drop support from your SQL 2000. SQL 2005 adds so many new   
   good features, that you will be programming with one hand tied behind   
   your back as long as you support SQL 2000.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
   SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)