|
learnsql123
Starting Member
USA
23 Posts |
 Posted - 11/06/2012 : 12:07:06
|
Here is my data:
Drop table #Temp
CREATE TABLE #Temp
(ID int, Proj varchar(5), lkey int, akey int, Gname varchar(5), pnum varchar(12))
Insert into #Temp
Values
(1, 'Proj1',988,4481,'aaa','12-1'),
(2, 'Proj1',757,4482,'bbb','12-1'),
(3, 'Proj1',617,4173,'ccc','12-3'),
(4, 'Proj1',493,4175,'ddd','12-3'),
(5, 'Proj1',404,4174,'ddd','12-3'),
(6, 'Proj1',284,4176,'eee','13-1'),
(7, 'Proj1',369,4177,'fff','14-3'),
(8, 'Proj1',440,4178,'ggg','15-6')
Select * from #Temp
I need to assign a groupNums as follows:
If they have same pNum value they get the same GropuNum
and all GroupNums will be sequential numbers
So the end result will be as follows:
ID Proj lkey akey Gname pnum GroupNum
1 Proj1 988 4481 aaa 12-1 1
2 Proj1 757 4482 bbb 12-1 1
3 Proj1 617 4173 ccc 12-3 2
4 Proj1 493 4175 ddd 12-3 2
5 Proj1 404 4174 ddd 12-3 2
6 Proj1 284 4176 eee 13-1 3
7 Proj1 369 4177 fff 14-3 4
8 Proj1 440 4178 ggg 15-6 5
How can I use PartitionBy or OrderBy clause to get this result?
Thanks for helping.
|
|
partition by or order by
