SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 partition by or order by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learnsql123
Starting Member

USA
23 Posts

Posted - 11/06/2012 :  12:07:06  Show Profile  Reply with Quote

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

India
52309 Posts

Posted - 11/06/2012 :  12:12:53  Show Profile  Reply with Quote

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


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

Go to Top of Page

learnsql123
Starting Member

USA
23 Posts

Posted - 11/06/2012 :  13:04:57  Show Profile  Reply with Quote
Thanks Visakh16. Works wonders.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2012 :  13:42:03  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000