title: How do I get the key of previous ordered rows with an unknown offset?
body: <p>I am working with an ERP that stores its retail sales and any discounts related to that sale as separate lines in the same table.</p>
  
  <p>Product lines are identified by a line type of 0 whereas discount lines are a line type of 6, and loyalty discount is a line type of 7.</p>
  
  <p>Unfortunately, the discount records do not have any direct relation back to the product line that the discount applies to.</p>
  
  <p>I need to extract this data and for the discount lines, provide the key for the product line that it applies to.</p>
  
  <p>Discount Lines (6 and 7) ALWAYS come up after 0 LineType and can be identified by the sequence number. For example, in my table below, the second and third line are discounts for the first line because they have a LineType of 6 and 7 that come after LineType 0.</p>
  
  <p><a href="http://sqlfiddle.com/#!3/80249" rel="nofollow">http://sqlfiddle.com/#!3/80249</a></p>
  
  <p>I need my results to look like this:</p>
  
  <pre><code>Id     LineType     SequenceNumber    Description              ProductLineId
  ---------------------------------------------------------------------
  1      0            1                 Product A                NULL
  2      6            2                 Discount on Product A    1
  3      7            3                 Loyalty Discount         1
  4      0            4                 Product B                NULL
  5      0            5                 Product C                NULL
  6      6            6                 Discount on Product C    5
  </code></pre>
  
  <p>I know I can use a <code>LAG</code> function, however the offset is not known, as there may be more discounts. I am currently using a cursor similar to the below which is working, but got awfully slow (the below is the same concept however it sets a Flag against the data, not the ID of the record:</p>
  
  <pre><code>OPEN SalesLines
  FETCH NEXT FROM SalesLines INTO @SourceKey, @HeaderSourceKey, @IsMarkedDownFlag, @LineSequenceNumber, @RetailLineTypeId
  
  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @RetailLineTypeId = 1 OR @CurrentHeaderSourceKey != @HeaderSourceKey
    BEGIN
      SET @CurrentIsMarkedDownFlag = @IsMarkedDownFlag
      SET @CurrentHeaderSourceKey = @HeaderSourceKey
    END
    ELSE
    BEGIN
      IF @CurrentIsMarkedDownFlag != @IsMarkedDownFlag
      BEGIN
        UPDATE @RawData
        SET IsMarkedDown = @CurrentIsMarkedDownFlag
        WHERE SourceKey = @SourceKey
      END
    END
  </code></pre>