partition by or order by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

partition by or order by

Author

Topic

learnsql123
Starting Member

23 Posts

Posted - 2012-11-06 : 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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 12:12:53

[code]
SELECT DENSE_RANK() OVER (PARTITION BY Proj ORDER BY pnum) AS GroupNum,*
FROM #temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

learnsql123
Starting Member

23 Posts

Posted - 2012-11-06 : 13:04:57

Thanks Visakh16. Works wonders.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-11-06 : 13:42:03

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources