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.
| Author |
Topic |
|
Liquid8
Starting Member
2 Posts |
 Posted - 2010-04-21 : 08:09:28
|
| Hi all,I'd need to fetch a ordernumber for project like this:1 PROJECT firstProject PERSON X1 PROJECT firstProject PERSON Y1 PROJECT firstProject PERSON Z2 PROJECT secondProject PERSON X2 PROJECT secondProject PERSON Y3 PROJECT thirdProject PERSON Y3 PROJECT thirdProject PERSON ZSo the problem is that how could I get the ordernumber of the project so that number of persons in it dont increase that surrogate value which happens for example with ROW_NUMBER() ?This is the query where I'd need to add it:Select proj_name, person_name from PROJECTINNER JOIN PERSON on PROJECT.PROJ_ID = PERSON.PROJ_ID |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-21 : 08:12:06
|
Table structure, sample data and wanted output in relation to sample data would be fine.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-21 : 09:01:39
|
| Use dense_rank()MadhivananFailing to plan is Planning to fail |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-21 : 23:36:38
|
| on what basis you want numbering to be done?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Liquid8
Starting Member
2 Posts |
Posted - 2010-04-22 : 02:44:16
|
| Thank you, that DENSE_RANK() was just what I needed.I wanted to rank the result according to project's name. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-22 : 04:50:47
|
| ok then DENSE_RANK() OVER(PARTITION BY ProjectName) will do------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-04-22 : 06:27:58
|
quote:
Originally posted by visakh16
ok then DENSE_RANK() OVER(PARTITION BY ProjectName) will do------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
My guess is OP was already using it with PARTITION BY ProjectName with row_number()MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
Fetching a ordernumber for "upper" table in query