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 |
|
Mopani
Yak Posting Veteran
55 Posts |
 Posted - 2009-06-19 : 21:52:51
|
| I wish to know if I can do the followingI have 4 columns of data. I wish to add to each column a ranking so that no matter on which column I sort, I can see what rank the previous or the next column has.I can sort these fields ascending or descending.If I sort field 2 descending, then field 1 is random.I would like to add a ranking number to each field so that I can easily sort any field, yet have a ranking number for the 3 other fields.In other words I wish to display 8 fields - Field 1 Data, Field 1 Ranking, Field 2 Data, Field 2 Ranking, etc.Example of what I wish to see:I sort field 2 descending (meaning the high value = rank 1) and I can see that for this record it ranks 5th in field 1, 3rd in field 3 and 10th in field 4. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-19 : 22:41:53
|
use row_number() or rank() or dense_rank() function. Check it out in BOL
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-20 : 01:46:24
|
| Are you using sql 2005? if yes, you can use above windowing functions. else, you need to simulate these functions yourself. |
|
|
|
Mopani
Yak Posting Veteran
55 Posts |
Posted - 2009-06-20 : 07:05:09
|
| Thanks, I got it working eventually by using rank()Much appreciated. |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-06-21 : 18:48:16
|
Heh... doesn't anyone share code anymore?--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] |
|
|
|
|
|
|
|
|
ranking columns of data - query wanted