Similar groups placed randomly must be allocated a group number each
-
Hey guys, good day 🙂
I was working on something, but could not pull off this thing.. I have done it in the past, but wasn't able to re-collect how i did it..
Sample Data
Source Table :
DECLARE @SourceTable TABLE
(
ID INT IDENTITY (1,1) ,
[Value] INT
)
INSERT INTO @SourceTable ([Value])
SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 2
UNION ALL SELECT 1
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 4
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
SELECT ID , [Value]
FROM @SourceTable
Expected Result:
DECLARE @ResultTable TABLE
(
ID INT IDENTITY (1,1) ,
[Value] INT ,
[SetNum] INT
)
INSERT INTO @ResultTable ([Value] , [SetNum])
SELECT 1 , 1
UNION ALL SELECT 1 , 1
UNION ALL SELECT 2 , 2
UNION ALL SELECT 2 , 2
UNION ALL SELECT 2 , 2
UNION ALL SELECT 2 , 2
UNION ALL SELECT 3 , 3
UNION ALL SELECT 2 , 4
UNION ALL SELECT 1 , 5
UNION ALL SELECT 4 , 6
UNION ALL SELECT 4 , 6
UNION ALL SELECT 4 , 6
UNION ALL SELECT 1 , 7
UNION ALL SELECT 1 , 7
UNION ALL SELECT 2 , 8
UNION ALL SELECT 2 , 8
UNION ALL SELECT 3 , 9
UNION ALL SELECT 3 , 9
UNION ALL SELECT 3 , 9
SELECT ID , [Value] , [SetNum]
FROM @ResultTable
As you can see , what i have a groups of rows with same value placed at an interval or rather placed randomly. Now i have to assign an incrementing Set Number for each group..
W.R.T sample data , we have
first 2 rows with value 1 --Expected Result : assign Set Num 1
next 4 rows with value 2 -- Expected Result : assign Set Num 2
next 1 row with value 3 -- Expected Result : assign Set Num 3
next 1 row with value 2 -- Expected Result : assign Set Num 4
next 1 row with value 1 -- Expected Result : assign Set Num 5
next 3 rows with value 4 -- Expected Result : assign Set Num 6
........ and so on..
Please suggest me a solution to do this!
TIA, guys!
-
hi coldcoffee just replace sourcetable with @sourcetable
Declare @ResultTable Table
(
ID int identity(1,1),
[value] int,
[setnumber] int
)
declare @Newvalue int
declare @OldValue int
declare @counter int
set @counter=0
declare abc cursor
for
select value from sourcetable
open abc
fetch next from abc into @NewValue
while @@fetch_status=0
begin
if @OldValue=@NewValue
begin
insert into @ResultTable ([value],[setnumber]) values (@NewValue,@counter)
end
else
begin
set @counter=@counter+1
insert into @ResultTable ([value],[setnumber]) values (@NewValue,@counter)
end
set @OldValue=@NewValue
fetch next from abc into @NewValue
end
close abc
deallocate abc
select * from @ResultTable
-
Hi ColdCoffee,
If you want to assign set number, based on the list of values, then every time the list of values can change, as we are not having ORDER BY clause here. Can we assume that ORDER BY is applied on ID column, when we pull the result, so we can be sure of values list will be same and we can write query accordingly. Otherwise, everytime the Set assignment can return different result set.
-
sayedkhalid99 (9/29/2010)
hi coldcoffee just replace sourcetable with @sourcetableDeclare @ResultTable Table
(
ID int identity(1,1),
[value] int,
[setnumber] int
)
declare @Newvalue int
declare @OldValue int
declare @counter int
set @counter=0
declare abc cursor
for
select value from sourcetable
open abc
fetch next from abc into @NewValue
while @@fetch_status=0
begin
if @OldValue=@NewValue
begin
insert into @ResultTable ([value],[setnumber]) values (@NewValue,@counter)
end
else
begin
set @counter=@counter+1
insert into @ResultTable ([value],[setnumber]) values (@NewValue,@counter)
end
set @OldValue=@NewValue
fetch next from abc into @NewValue
end
close abc
deallocate abc
select * from @ResultTable
Sayed, thanks a lot , mate.... BUt i have tried without any loops before.. i also participated in one thread with the similar request and i remember one reply using a difference of ID values to get the groups.. i cant get to work it now :(.. anyways, thanks a lot man..
-
Venkataraman R (9/29/2010)
Hi ColdCoffee,If you want to assign set number, based on the list of values, then every time the list of values can change, as we are not having ORDER BY clause here. Can we assume that ORDER BY is applied on ID column, when we pull the result, so we can be sure of values list will be same and we can write query accordingly. Otherwise, everytime the Set assignment can return different result set.
Yes, Venkat, use ID For ORDERing.. "Values" column must be persisted as it is in the sample data..
-
Hi ColdCoffee
Funny - I've had to do this recently in a production system but I couldn't find the code or even remember the method. Age sucks.
Here's something which works to tide you over:
;WITH Calculator AS (SELECT s.ID, s.[Value], Setnum = 1
FROM @SourceTable s
WHERE ID = 1
UNION ALL
SELECT s.ID, s.[Value], Setnum = CASE WHEN s.[Value] = c.[Value] THEN c.Setnum + 1 ELSE 1 END
FROM @SourceTable s
INNER JOIN Calculator c ON c.ID+1 = s.ID
) SELECT ID, [Value], Setnum FROM Calculator
So long as ID is sequential in the order in which you expect your groups to materialise, and there are no gaps in the sequence, you'll be just fine.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Maybe this?
WITH CTE1 AS (SELECT ID,Value,
ID-ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) AS rn
FROM @SourceTable),
CTE2 AS (
SELECT ID,
Value,
MIN(ID) OVER(PARTITION BY rn,Value) AS minID
FROM CTE1)
SELECT ID,
Value,
DENSE_RANK() OVER(ORDER BY minID) AS SetNum
FROM CTE2
ORDER BY ID;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Chris Morris-439714 (9/29/2010)
Hi ColdCoffeeFunny - I've had to do this recently in a production system but I couldn't find the code or even remember the method. Age sucks.
Here's something which works to tide you over:
;WITH Calculator AS (SELECT s.ID, s.[Value], Setnum = 1
FROM @SourceTable s
WHERE ID = 1
UNION ALL
SELECT s.ID, s.[Value], Setnum = CASE WHEN s.[Value] = c.[Value] THEN c.Setnum + 1 ELSE 1 END
FROM @SourceTable s
INNER JOIN Calculator c ON c.ID+1 = s.ID
) SELECT ID, [Value], Setnum FROM Calculator
So long as ID is sequential in the order in which you expect your groups to materialise, and there are no gaps in the sequence, you'll be just fine.
I guess i am younger than you ( me, a 25 yrs old) , but still i cant remember the time i coded a request similar to that ; so , you forgetting is completely fair 🙂
And Chris, your solution actually numbers sequentially each row in a group, resetting the counter and starting from 1 for the next... That's not specifically what i am after 🙂 anyways, thanks , CM, i appreciate your effort, thanks a lot buddy 🙂
-
Mark-101232 (9/29/2010)
Maybe this?WITH CTE1 AS (SELECT ID,Value,
ID-ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) AS rn
FROM @SourceTable),
CTE2 AS (
SELECT ID,
Value,
MIN(ID) OVER(PARTITION BY rn,Value) AS minID
FROM CTE1)
SELECT ID,
Value,
DENSE_RANK() OVER(ORDER BY minID) AS SetNum
FROM CTE2
ORDER BY ID;
Ah, here it is.. i guess, it was u Mark, that coded in the other thread as wel.. This is what i meant, a subtraction of ID to the MIN.. this gives exactly what i want.. thanks , mark 🙂 superrr
-
ColdCoffee (9/29/2010)
...And Chris, your solution actually numbers sequentially each row in a group, resetting the counter and starting from 1 for the next... That's not specifically what i am after 🙂 anyways, thanks , CM, i appreciate your effort, thanks a lot buddy 🙂Oops sorry! Here you go...
;WITH Calculator AS (SELECT s.ID, s.[Value], Setnum = 1
FROM @SourceTable s
WHERE ID = 1
UNION ALL
SELECT s.ID, s.[Value], Setnum = CASE WHEN s.[Value] = c.[Value] THEN c.Setnum ELSE c.Setnum + 1 END
FROM @SourceTable s
INNER JOIN Calculator c ON c.ID+1 = s.ID
) SELECT ID, [Value], Setnum FROM Calculator
Mark's solution is likely to be far more efficient.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ColdCoffee (9/29/2010)
Mark-101232 (9/29/2010)
Maybe this?WITH CTE1 AS (SELECT ID,Value,
ID-ROW_NUMBER() OVER(PARTITION BY Value ORDER BY ID) AS rn
FROM @SourceTable),
CTE2 AS (
SELECT ID,
Value,
MIN(ID) OVER(PARTITION BY rn,Value) AS minID
FROM CTE1)
SELECT ID,
Value,
DENSE_RANK() OVER(ORDER BY minID) AS SetNum
FROM CTE2
ORDER BY ID;
Ah, here it is.. i guess, it was u Mark, that coded in the other thread as wel.. This is what i meant, a subtraction of ID to the MIN.. this gives exactly what i want.. thanks , mark 🙂 superrr
Thanks for the feedback!
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Here's the other thread we were all thinking of:
http://www.sqlservercentral.com/Forums/FindPost931414.aspx
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Chris Morris-439714 (9/29/2010)
Here's the other thread we were all thinking of:U are a rock-star CM... thats what exactly i was saying about...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply