| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
194 Posts |
 Posted - 11/29/2012 : 04:38:20
|
Hello all
i have table like users
UserId DefinitionId ConditionId UserName
1 1 1 joseph
2 1 1 khan
3 1 1 gary
4 1 1 brown
5 2 2 Jim
6 2 2 jay
my output should come like this one :
UserId DefinitionId ConditionId UserName
1 1 1 joseph
2 1 2 khan
3 1 3 gary
4 1 4 brown
5 2 1 Jim
6 2 2 jay
here condition id should get numbers like 1,2,3,4 etc for definition ID which as 1,1,1,1,2,2
P.V.P.MOhan |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 11/29/2012 : 04:49:31
|
DECLARE @tab TABLE(UserId int, DefinitionId int, ConditionId int, UserName varchar(10))
INSERT INTO @tab
SELECT 1, 1, 1, 'joseph' union all
SELECT 2, 1, 1, 'khan' union all
SELECT 3, 1, 1, 'gary' union all
SELECT 4, 1, 1, 'brown' union all
SELECT 5, 2, 2, 'Jim' union all
SELECT 6, 2, 2, 'jay'
;with cte AS (SELECT *, ROW_NUMBER() over(partition by definitionid order by userid) rn FROM @tab)
update t SET t.ConditionId = c.rn
FROM @tab t JOIN cte c ON t.UserId = c.UserId
SELECT * FROM @tab
--
Chandu |
 |
|
|
mohan123
Posting Yak Master
India
194 Posts |
Posted - 11/29/2012 : 05:16:11
|
hey chandu asd in your scenario its correct but i gave my question wrong..conditionid is all 1,1,1,1,1 not 1,1,1,1,2,2 see condition ID suggest me
P.V.P.MOhan |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 11/29/2012 : 05:31:24
|
This code is independent of ConditionIds
DECLARE @tab TABLE(UserId int, DefinitionId int, ConditionId int, UserName varchar(10))
INSERT INTO @tab
SELECT 1, 1, 1, 'joseph' union all
SELECT 2, 1, 1, 'khan' union all
SELECT 3, 1, 1, 'gary' union all
SELECT 4, 1, 1, 'brown' union all
SELECT 5, 2, 1, 'Jim' union all
SELECT 6, 2, 1, 'jay'
;with cte AS (SELECT *, ROW_NUMBER() over(partition by definitionid order by userid) rn FROM @tab)
update t SET t.ConditionId = c.rn
FROM @tab t JOIN cte c ON t.UserId = c.UserId
SELECT * FROM @tab
--OUTPUT
UserId DefinitionId ConditionId UserName
1 1 1 joseph
2 1 2 khan
3 1 3 gary
4 1 4 brown
5 2 1 Jim
6 2 2 jay
--
Chandu |
|
|
| |
Topic |
|
|
|
How to update in this scenario???
