Solved

Using TSQL to identify sequencing of groups

Posted on 2016-08-23
9
47 Views
Last Modified: 2016-08-30
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
Comment
Question by:stefanlennerbrant
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 125 total points
ID: 41766815
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41766818
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 41766831
which version of MS SQL?
0
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 125 total points
ID: 41766861
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:stefanlennerbrant
ID: 41766914
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
 
LVL 23

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41769825
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
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41770156
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41770208
just to mention the LEAD/LAG version only works as from SQL 2012 on:
https://msdn.microsoft.com/en-us/library/hh231256.aspx
0
 

Author Closing Comment

by:stefanlennerbrant
ID: 41776389
Great, spot on! Thanks!
All your suggestions work great, but I'm forced to split points in some way...
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select - column value lookup. 3 40
SQL Login 17 37
T-SQL:  Negative Numbering in CTE Is Not Working 2 27
TSQL DateADD update Question 4 24
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now