SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sequence-numbering groups


Sequence-numbering groups

Author
Message
gmrose
gmrose
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 278
I have a table of checks, each of which has a Status of 'Open' or 'Void'. Below are some sample data.

CKID Stat
101 Open
102 Open
103 Open
104 Void
105 Void
106 Open
107 Open
108 Open
109 Void
110 Open

I would like to create a CTE that would include a column for each group of checks, as shown below.

CKID Stat Grp
101 Open 1
102 Open 1
103 Open 1
104 Void 2
105 Void 2
106 Open 3
107 Open 3
108 Open 3
109 Void 4
110 Open 5

Thank you for any help.
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11039 Visits: 7660
A serial update might work for this, but the implementation would require me to see the underlying DDL for the table and indexing, specifically the clustered index.

For the proper rules to doing something like this, do a search for 'Quirky Update' here on SSC and you'll find a very detailed article on how to do something like that.

EDIT: found it: http://www.sqlservercentral.com/articles/T-SQL/68467/


Other than that, you'll have to make data-islands of the data and then perhaps setup a row_number() over on the subset. I'll have to chew on that one a while.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
LutzM
LutzM
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13271 Visits: 13559
It's slightly more complicated than just using a CTE...

DECLARE @tbl TABLE
(
ckid INT,stat VARCHAR(10)
)
INSERT INTO @tbl
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open'

SELECT *
FROM @tbl

;WITH cte AS -- row number per group
(
SELECT
*,
ROW_NUMBER() OVER(ORDER BY ckid) r1,
ROW_NUMBER() OVER(ORDER BY ckid)-
ROW_NUMBER() OVER(PARTITION BY stat ORDER BY ckid ) AS ROW
FROM @tbl
),cte2 AS -- row number per group range, ordered by ckid
(
SELECT MIN(r1) mi,MAX(r1) ma,stat,ROW, ROW_NUMBER() OVER(ORDER BY MIN(r1) ) r2
FROM cte
GROUP BY stat,ROW
)
SELECT ckid ,cte.stat,cte2.r2
FROM cte
INNER JOIN cte2 ON r1>=mi AND r1<=ma
ORDER BY r1





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
gmrose
gmrose
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 278
Thank you both for your help, Craig for the interesting article which I have started reading, and Lutz for the solution I needed.

gmrose
Evil Kraig F
Evil Kraig F
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11039 Visits: 7660
Lutz, that is wild. I like it. Thanks. w00t

Have you done that on huge recordsets? Does it perform well?


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
LutzM
LutzM
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13271 Visits: 13559
Craig Farrell (10/20/2010)
Lutz, that is wild. I like it. Thanks. w00t

Have you done that on huge recordsets? Does it perform well?


No, I haven't. And I probably won't either.
Such requirements and some rather large tables actually call for the quirky update from my point of view.
I don't expect the CTE to perform anywhere near the quirky update. Mainly because of the triple sort operation, the aggregation and the join on a range. But it should outperform any loop.

So, why did I posted this solution anyway?
#1: You already posted the link to Jeffs article.
#2: I did pretty much what you've already described. Just using T-SQL over English ;-):-D
#3: gmrose specifically asked for a cte solution and
#4: it was some kind of an exercise for me to do it using a non-quirky-update method.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
gmrose
gmrose
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 278
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.

Thanks anyways.

gmrose
WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12567 Visits: 10602
Looks like it's time for a quirky update solution:
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;
-- This primary key is crucial. If your table doesn't have a clustered index
-- on this, then you will have to dump the data to a temp table (along with
-- the PK columns of the table), use this PK, then update the real table
-- by joining this temp table back to the real table by the PK columns.
CREATE TABLE #temp (ckid INT PRIMARY KEY CLUSTERED, stat varchar(10), Grp int);
INSERT INTO #temp (ckid, stat)
SELECT 101 ,'Open' UNION ALL
SELECT 102 ,'Open' UNION ALL
SELECT 103 ,'Open' UNION ALL
SELECT 104 ,'Void' UNION ALL
SELECT 105 ,'Void' UNION ALL
SELECT 106 ,'Open' UNION ALL
SELECT 107 ,'Open' UNION ALL
SELECT 108 ,'Open' UNION ALL
SELECT 109 ,'Void' UNION ALL
SELECT 110 ,'Open';

-- declare and initialize variables needed in the update statement.
DECLARE @Sequence int, -- for safety check
@stat varchar(10), -- to hold stat column from last row
@grp int, -- current grp number
@ckid int; -- for anchor column
SET @Sequence = 1;
SET @grp = 1;

/*
This form of the UPDATE statement has some rules for proper usage.
See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
for a complete discussion of how this works, and all of the rules for utilizing it.
If you don't follow ALL the rules, you WILL mess up your data.
*/
WITH SafeTable AS
(
-- generate table with a sequence column in clustered index order
-- in order to verify that update is happening in the correct order
SELECT ckid,
stat,
grp,
Sequence = ROW_NUMBER() OVER (ORDER BY ckid)
FROM #temp
)
UPDATE t
-- verify in proper sequence order; if not, throw an error so nothing is updated
SET @grp = grp = CASE WHEN Sequence = @Sequence THEN
CASE WHEN stat <> @Stat THEN @grp + 1
-- if you have to separate this by account numbers also, then you will need
-- a when clause to handle it here also.
-- different stat --> increment grp number
ELSE @grp END -- same stat --> same grp number
ELSE 1/0 END, -- not in proper sequence order, so throw an error
@Sequence = @Sequence + 1,
@stat = stat, -- get the current value to compare to in next row
@ckid = ckid -- anchor column
FROM SafeTable t WITH (TABLOCKX) -- lock table
OPTION (MAXDOP 1); -- prevent parallelism!

select * from #temp;



Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

ColdCoffee
ColdCoffee
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4755 Visits: 5550
Wayne, in ur solution, you are basing your order over ckid column (Sequence = ROW_NUMBER() OVER (ORDER BY ckid) )
What if ckid is not in a proper order ? what the OP wants is to maintain the open-void in tact right.. so if you row_number it over ckid, then would that open-void combo be maintained ?

P.S: i am still learning the QU, so please pardon my ignorance..
LutzM
LutzM
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13271 Visits: 13559
gmrose (10/20/2010)
After I finish reading the article, I will need to build a new statement using its suggestions. The one that I built based on Lutz's solution has been running for over 30 minutes so far and hasn't finished yet.

Thanks anyways.

gmrose


How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search