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 |
|
mavershang
Posting Yak Master
111 Posts |
 Posted - 2008-12-19 : 14:06:25
|
| Hi all. I am a newbie to SQL.Here is my question.col1 col2 col3a 24 1.9b 21 2.01......I want to write a query to get the rank percentage based on col3 value.for example: if there are 100 records in that table, and the row (a,24,1.9) is ranked highest according to the col3 value(1.9), I want it to be (a,24,1.9,1%).so it is like:select col1, col2, col3, ???from tableThanks. |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2008-12-19 : 22:50:59
|
| use rank,dense_rank functionsselect *,rank()over(order by col3) from urtable select *,dense_rank()over(order by col3) from urtable Just sample Queries try like this |
 |
|
|
Jai Krishna
Constraint Violating Yak Guru
333 Posts |
Posted - 2008-12-19 : 22:51:49
|
| select col1,col2,col3,CAST(ROW_NUMBER() OVER(ORDER BY col3) AS VARCHAR(22))+'%' AS Rank FROM tableJai Krishna |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-12-20 : 04:53:37
|
SELECT *,100.0E * ROW_NUMBER() OVER (ORDER BY Col3) / COUNT(*) OVER ()FROM Table1
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
|
|
|
|
Help! how to calculate the rank percentage