• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Using TSQL to identify sequencing of groups

I've got an MS-SQL table
create table test(
  name varchar(100),
  sequence integer,
  position integer);

Open in new window


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);

Open in new window


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);

Open in new window


Any smart ideas? Otherwise I'll probably have to do this "off-line" with some kind of non-SQL programming

/Stefan
0
Stefan Lennerbrant
Asked:
Stefan Lennerbrant
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Believe it or not, I had written an article about this kind of problems, see here:
https://www.experts-exchange.com/articles/3952/ranges-gaps-overlaps-for-number-and-date-ranges.html
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the only thing you will need to add, over the article suggestion, is in the row_number() functions to add this:
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
0
 
Éric MoreauSenior .Net ConsultantCommented:
which version of MS SQL?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Éric MoreauSenior .Net ConsultantCommented:
could it be:
;WITH C AS
(
  SELECT name, position
      , DENSE_RANK() OVER(PARTITION BY name ORDER BY position, name) AS grp
	  , ROW_NUMBER() OVER(ORDER BY position) AS L
	  , ROW_NUMBER() OVER(ORDER BY position) - RANK() OVER(PARTITION BY name ORDER BY position) AS T 
  FROM test 
)

SELECT name, MIN(position), MAX(position)
FROM C
GROUP BY name, T

Open in new window

0
 
Stefan LennerbrantAuthor Commented:
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
0
 
Pawan KumarDatabase ExpertCommented:
SELECT
              Name
            , MIN(LEVEL) StartPosition
            , MAX(LEVEL) EndPosition
FROM
(
              SELECT
                          Name
                        , LEVEL
                        , ROW_NUMBER() OVER(ORDER BY LEVEL) - ROW_NUMBER() OVER(PARTITION BY Name ORDER BY LEVEL) rnk      
              FROM THEGapPuzzle
)r
GROUP BY Name,rnk

Pawan Khowal
MSBISKILLS.COM
0
 
Pawan KumarDatabase ExpertCommented:
Well the methods given above will NOT scale when data will grow..

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

Open in new window

Enjoy !
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just to mention the LEAD/LAG version only works as from SQL 2012 on:
https://msdn.microsoft.com/en-us/library/hh231256.aspx
0
 
Stefan LennerbrantAuthor Commented:
Great, spot on! Thanks!
All your suggestions work great, but I'm forced to split points in some way...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now