Hi all,
So we all know well that poorly architected tables can lead to us having to do double backflips in SQL to try to get the data, right??
Does anyone have any tricks for grouping subsequent records that follow a contiguous sequence and then breaking to the next group when the sequence has a gap? In the example below. I need to group 110 - 113 together and 0117-0118 together somehow. My last good idea fizzled when I found that I could not use a sequence object inside a case statement (for next or previous value). It seems that Dense_Rank could work but without it tracking a sequential increment, it hasn't worked for me yet.
Data Example:
SeqCd Comnt
1131 Now is the time
1132 for all good men
1133 to come to the aid
1137 just a note to let
1138 you know that we
1139 won’t be able to
1313 where did this come
I have used LAG and LEAD to determine previous and next SeqCd values but it hasn’t helped me do much more than identify where the new groups would start.
With mydata as(
SELECT SeqCd,
LAG(SeqCd,1) OVER(ORDER BY SeqCd) AS Prior_Seq,
LEAD(SeqCd,1,-1) OVER(ORDER BY SeqCd) AS Next_Seq
FROM rmcz
ORDER BY SeqCd
)
,foraft as(
Select a.*,
case when SeqCd = prior_seq +1
then 'N'
else 'Y'
end as NewGroup
from mydata a
)
select * from foraft ORDER BY SeqCd;
SeqCD Prior Next NewGroup
Thanks for any help!
Cliff Dowell
So we all know well that poorly architected tables can lead to us having to do double backflips in SQL to try to get the data, right??
Does anyone have any tricks for grouping subsequent records that follow a contiguous sequence and then breaking to the next group when the sequence has a gap? In the example below. I need to group 110 - 113 together and 0117-0118 together somehow. My last good idea fizzled when I found that I could not use a sequence object inside a case statement (for next or previous value). It seems that Dense_Rank could work but without it tracking a sequential increment, it hasn't worked for me yet.
Data Example:
SeqCd Comnt
1131 Now is the time
1132 for all good men
1133 to come to the aid
1137 just a note to let
1138 you know that we
1139 won’t be able to
1313 where did this come
I have used LAG and LEAD to determine previous and next SeqCd values but it hasn’t helped me do much more than identify where the new groups would start.
With mydata as(
SELECT SeqCd,
LAG(SeqCd,1) OVER(ORDER BY SeqCd) AS Prior_Seq,
LEAD(SeqCd,1,-1) OVER(ORDER BY SeqCd) AS Next_Seq
FROM rmcz
ORDER BY SeqCd
)
,foraft as(
Select a.*,
case when SeqCd = prior_seq +1
then 'N'
else 'Y'
end as NewGroup
from mydata a
)
select * from foraft ORDER BY SeqCd;
SeqCD Prior Next NewGroup
1131 | - | 1132 | Y |
1132 | 1131 | 1133 | N |
1133 | 1132 | 1137 | N |
1137 | 1133 | 1138 | Y |
1138 | 1137 | 1139 | N |
1139 | 1138 | 1313 | N |
1313 | 1139 | -1 | Y |
Cliff Dowell
Comment