Assign numeric values for the alphanumeric cases based on maximum value

Question

I have a table as below

ID	Version	City	Notes
101	1	Sweden	01
101	1	Berlin	20
101	1	Tokyo	AAB
101	1	Delhi	C23
101	1	Durban	34
101	2	Berlin	0
101	2	Vatican	AB
101	2	Mexico	329

Expected output

ID	Version	City	Notes
101	1	Sweden	01
101	1	Berlin	20
101	1	Durban	34
101	1	Tokyo	44
101	1	Delhi	54
101	2	Berlin	0
101	2	Mexico	329
101	2	Vatican	330

Logic Whenever i find values that are other than numeric [0-9], i have to get maximum value in the specific group(ID,Version) and increment 10 to all the remaining rows. If we see the first group(101,1), I have 2 alphanumeric rows. So i have to take max which is 34 and increment by 10 for each of the remaining rows(44 & 54 - order of the remaining rows doesn't matter).

I have tried below code

select ID,Version,
MAX(case when ISNUMERIC(Notes)=1 then Notes+10 end) OVER (PARTITION BY ID, Version ORDER BY City)
FROM MYTABLE

But this returns the same value(44) for all remaining rows. I have also tried ROW_NUMBER()..+MAX(Case...) but that as well not giving the expected outcome. Please guide me here.

Answer 1

We can achieve this using the the window functions MAX() and SUM() :

SELECT ID, Version, City,
          CASE WHEN  ISNUMERIC(Notes) = 0 
               THEN ISNULL(max_Notes, 0) + sum(step) OVER (PARTITION BY ID, Version ORDER BY City, rn)
               ELSE Notes
          END AS Notes
FROM (
  SELECT *, max(CASE WHEN ISNUMERIC(Notes)=1 THEN Notes END) OVER (PARTITION BY ID, Version) AS max_Notes,
            CASE WHEN  ISNUMERIC(Notes)=0 THEN 10 END AS step,
            ROW_NUMBER() OVER (PARTITION BY ID, Version ORDER BY City) AS rn
  FROM MYTABLE
) as s
ORDER BY ID, Version, Notes;

The subquery will get the highest note within each group and assign the sequential step for every alphanumeric value, whereas the outer query will compute the notes for each alphanumeric row based on the max note and the running total on step ordered by City ( If you wish to assign distinct values to duplicate cities within the same group, it's necessary to also order by row number.).

Which is for this dataset :

INSERT INTO mytable VALUES
(101,   1,  'Sweden',   '01'),
(101,   1,  'Berlin',   '20'),
(101,   1,  'Tokyo',    'AAB'),
(101,   1,  'Delhi',    'C23'),
(101,   1,  'Durban',   '34'),
(101,   2,  'Berlin',   '0'),
(101,   2,  'Vatican',  'AB'),
(101,   2,  'Mexico',   '329'),
(101,   3,  'New york', 'AC'),
(101,   3,  'Berlin',   'AB'),
(101,   4,  'Paris',    'AC'),
(101,   4,  'Paris',    '25'),
(101,   4,  'Paris',    'AB');

Results :

ID  Version City    Notes
101 1   Sweden      1
101 1   Berlin      20
101 1   Durban      34
101 1   Delhi       44
101 1   Tokyo       54
101 2   Berlin      0
101 2   Mexico      329
101 2   Vatican     339
101 3   Berlin      10
101 3   New york    20
101 4   Paris       25
101 4   Paris       35
101 4   Paris       45

Demo here