MSSQL - find immediate past iteration of field Bit of a difficult to explain one here, I have a table with product SKUs of different generations (or iterations) and I need to identify the most recent iteration that exists for each.
Timeline: Product X gets entered into the table, after a few months product X gets a revision and X(B) appears and is entered, sometimes iterations or revisions get skipped in this table. This is always done in an alphabetic ascending way - (B) is the first iteration, (C) follows, etc.
I need to identify what the immediate past iteration is for every one of these.
Example data set in column A and desired result in column B - can this be achieved or is it an impossible task?
Thank you in advance.
SKU; previous iteration (if applicable);
ABCD; ; 
ABCD(B); ABCD;
ABCD(E); ABCD(B);
ABDX(B); ABDX;
ABDX(F); ABDX(B);
ABDX; ;
BCDF(P); BCDF(C);
BCDF(C); ;  
BCDX; ; 
BCDX(B);BCDX;
BCDX(G);BCDX(B);
If you're using SQL Server 2012 or later you can use LAG, although you'll need to handle the lack of normalization first:
SELECT
  SKU
 ,CASE
    WHEN SKUPrefix &lt;&gt; LAG(SKUPrefix,1,'') OVER (PARTITION BY 1 ORDER BY SKU) THEN ''
    ELSE LAG(SKU) OVER (PARTITION BY 1 ORDER BY SKU)
  END AS PriorSKU
FROM
  (
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
  FROM
    &lt;Your Table&gt;
  ) SKUFix

One might as &quot;Why not partition by SKUPrefix and avoid the CASE statement and the answer is if a suitable index of the table (clustered or otherwise) starts with SKU then the query will be able to execute without sorting the data.
Prior to 2012 things are... uglier:
WITH SKUTemp AS
(
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
   ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SKU) AS SKURank
  FROM
    &lt;Your Table&gt;
)
SELECT
  SKU.SKU
 ,PriorSKU.SKU AS PriorSKU
FROM
  SKUTemp SKU
LEFT JOIN
  SKUTemp PriorSKU
    ON PriorSKU.SKUPrefix = SKU.SKUPrefix
        AND PriorSKU.SKURank = SKU.SKURank - 1

DDL to test:
CREATE TABLE #SKUTest
(
  SKU   CHAR(8)   NOT NULL
)
GO

INSERT INTO #SKUTest
VALUES
  ('ABCD')
 ,('ABCD(B)')
 ,('ABCD(E)')
 ,('ABDX')
 ,('ABDX(B)')
 ,('ABDX(F)')
 ,('BCDF(C)')
 ,('BCDF(P)')
 ,('BCDX')
 ,('BCDX(B)')
 ,('BCDX(G)')
GO

Results:
SKU         | PriorSKU
ABCD        | NULL
ABCD(B)     | ABCD    
ABCD(E)     | ABCD(B) 
ABDX        | NULL
ABDX(B)     | ABDX    
ABDX(F)     | ABDX(B) 
BCDF(C)     | NULL
BCDF(P)     | BCDF(C) 
BCDX        | NULL
BCDX(B)     | BCDX    
BCDX(G)     | BCDX(B)