question

Fozie avatar image
Fozie asked

Need help with doing a weighted ranking

First I am not new to SQL however I am no expert. I have a report I was tasked with creating, I am ranking 5 separate columns all numerical. This query works great assuming all columns are of equal importance. select RANK() OVER(order by f1.Col1) + RANK() OVER(order by f1.Col2) + RANK() OVER(order by f1.Col3) + RANK() OVER(order by f1.Col4) + RANK() OVER(order by f1.Col5)/5 as finalrank From table1 But what I am needing is to add weight to the columns IE Col1 is weighted at 1 Col2 is weighted at .8 Col3 is weighted at .6 Col4 is weighted at .4 Col5 is weighted at .2 Is this even possible?
rankranking
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KenJ avatar image
KenJ answered
Sure it's possible. You already did it with `f1.Col5`
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fozie avatar image Fozie commented ·
That is taking the sum of all ranks and just dividing them by the total ranks. I am needing the individual columns weighted then ranked by the total.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Surprise! division takes place before addition, even if the division operator comes "last" - http://msdn.microsoft.com/en-us/library/ms190276.aspx You don't have any parentheses that force the ranks to be added before being divided, so Col5 is being divided before being added to the other ranks. check out the different results between the following four queries: select rank() over (order by database_id ) + rank() over (order by compatibility_level)/5 , * from sys.databases select (rank() over (order by database_id ) + rank() over (order by compatibility_level))/5 , * from sys.databases select rank() over (order by database_id ) + rank() over (order by compatibility_level)/5.0 , * from sys.databases select (rank() over (order by database_id ) + rank() over (order by compatibility_level))/5.0 , * from sys.databases
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.