asked on Using TSQL to identify sequencing of groups
I've got an MS-SQL table
that contains:
The field "position" is unique, no two rows contain the same value. There may however be gaps in the number list (see above)
Now what I'd like to do with TSQL is to identify the "groupings" of sequences of the "position" number, for each name.
The name "A" first sequence group is for position 1,2,3 - its sequence 2 is for position 11,15 and its sequence 3 is for position 22,23
The "B" name sequence group 1 contains position 4,10 and its sequence 2 is for position 16
And the "C" name has only got one sequence group, for position 20,21
Thus, I'd like to the result of the TSQL update be identical to if I had done the following insert to begin with:
Any smart ideas? Otherwise I'll probably have to do this "off-line" with some kind of non-SQL programming
/Stefan
create table test(
name varchar(100),
sequence integer,
position integer);
that contains:
insert into test values('A', null, 1);
insert into test values('A', null, 2);
insert into test values('A', null, 3);
insert into test values('B', null, 4);
insert into test values('B', null, 10);
insert into test values('A', null, 11);
insert into test values('A', null, 15);
insert into test values('B', null, 16);
insert into test values('C', null, 20);
insert into test values('C', null, 21);
insert into test values('A', null, 22);
insert into test values('A', null, 23);
The field "position" is unique, no two rows contain the same value. There may however be gaps in the number list (see above)
Now what I'd like to do with TSQL is to identify the "groupings" of sequences of the "position" number, for each name.
The name "A" first sequence group is for position 1,2,3 - its sequence 2 is for position 11,15 and its sequence 3 is for position 22,23
The "B" name sequence group 1 contains position 4,10 and its sequence 2 is for position 16
And the "C" name has only got one sequence group, for position 20,21
Thus, I'd like to the result of the TSQL update be identical to if I had done the following insert to begin with:
insert into test values('A', 1, 1);
insert into test values('A', 1, 2);
insert into test values('A', 1, 3);
insert into test values('B', 1, 4);
insert into test values('B', 1, 10);
insert into test values('A', 2, 11);
insert into test values('A', 2, 15);
insert into test values('B', 2, 16);
insert into test values('C', 1, 20);
insert into test values('C', 1, 21);
insert into test values('A', 3, 22);
insert into test values('A', 3, 23);
Any smart ideas? Otherwise I'll probably have to do this "off-line" with some kind of non-SQL programming
/Stefan
Microsoft SQL ServerSQL
Last Comment
Stefan Lennerbrant
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
which version of MS SQL?
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Wow, such first class answers!
I'll read those articles, lots of thanks!
And @emoreau, your suggestion kind of works out-of-the-box as well, almost - perfect.
I'll just have to get that T number to go sequentially 1,2,3,... for each "name".
/Stefan
I'll read those articles, lots of thanks!
And @emoreau, your suggestion kind of works out-of-the-box as well, almost - perfect.
I'll just have to get that T number to go sequentially 1,2,3,... for each "name".
/Stefan
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Well the methods given above will NOT scale when data will grow..
Use below method. It will improve the performance heavily.
Use below method. It will improve the performance heavily.
SELECT Name, StartPosition, EndPosition FROM
(
SELECT Name,LeadValue, LagValue,position StartPosition, CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN LEAD(position) OVER (ORDER BY ( select 1)) ELSE position END EndPosition
FROM
(
SELECT
name
,CASE (LAG(name) OVER (ORDER BY ( select 1 ))) WHEN name THEN 1 ELSE 0 END LagValue
,CASE (LEAD(name) OVER (ORDER BY ( select 1))) WHEN name THEN 1 ELSE 0 END LeadValue
,position
FROM test
) tbl2
WHERE tbl2.LagValue = 0 OR tbl2.LeadValue = 0
) tbl3
WHERE LagValue=0
just to mention the LEAD/LAG version only works as from SQL 2012 on:
https://msdn.microsoft.com/en-us/library/hh231256.aspx
https://msdn.microsoft.com/en-us/library/hh231256.aspx
ASKER
Great, spot on! Thanks!
All your suggestions work great, but I'm forced to split points in some way...
All your suggestions work great, but I'm forced to split points in some way...
row_number() over ( partition by name order by position )
once you create a "staging table" with the proper values, you can then use this other article I wrote to update the values as needed:
https://www.experts-exchange.com/articles/1517/UPDATES-with-JOIN-for-everybody.html