Reputation: 39
I need to create a sequence based on the data in a column, the sample table is as follows:
id | group
101| bfd
102| bfd
123| klm
563| kln
693| klm
The sequence to be provided is like, if group = bfd then new_id = id + sequence in <0-200> if group = klm or kln then new_id = id + sequence in <201-499>
The output should be something like:
id | group | new_id
101| bfd | 1010
102| bfd | 1021
123| klm | 123201
563| kln |563202
693| klm | 693203
I tried the following code
select * from(
SELECT id, group,
CASE
WHEN group in 'bfd' THEN concat(ID, -1 +ROW_NUMBER() OVER(PARTITION BY group order by ID))
WHEN group='klm' or group="kln" THEN concat(ID, 200 + ROW_NUMBER() OVER(PARTITION BY group order by id))
END as new_id
FROM table2)
order by id;
How do I provide the limiting condition for row numbers to stop at 200 for particular group?
Upvotes: 0
Views: 887
Reputation: 74740
Move the concatting to the outer query:
select *, CASE WHEN new_id_bit > 200 THEN ... ELSE concat(ID, CASE WHEN group = 'bfd' THEN -1 ELSE 200 END + NEW_id_bit) end
FROM(
SELECT id, group,ROW_NUMBER() OVER(PARTITION BY group order by ID) as new_id_bit
FROM table2)x
order by id;
I don't know what you want to do if it's over 200; put your own logic in place of ... , for example CASE WHEN new_id_bit > 200 THEN 200 ELSE new_id_bit END
You could also/instead use a where clause to restrict to only those with a new ID bit less than 200
Upvotes: 2
Reputation: 1665
select t1.id,
t1.g,
convert(varchar(3), t1.id)
+ convert ( varchar(3),
case when g = 'bfd' then ROW_NUMBER() over ( partition by case when g = 'bfd' then 1 else 2 end order by id) - 1
else (ROW_NUMBER() over ( partition by case when g = 'bfd' then 1 else 2 end order by id)) + 200 end)
from T t1
output
101 bfd 1010
102 bfd 1021
123 klm 123201
563 kln 563202
693 klm 693203
Upvotes: 0