From: isgdre@HotMail.com   
      
   Erland Sommarskog wrote:   
   > 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   
   >   
   Nice Idea.  ProductID is not ordered and there can be duplicates but I   
   get the idea and I'll run with that one.   
   Thank.s   
      
   > 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.   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)