CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Chang
Chang

Reputation: 39

Sequence based on the condition using SQL

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

Answers (2)

Caius Jard
Caius Jard

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

AIMIN PAN
AIMIN PAN

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

Related Questions