ranking based on sorting 1

[jacob94]

I have the following data in a table:
name wins diff pct pts
admin14 10 0 41 77
admin11 10 1 42 777
admin12 10 24 41 4443
administrator 10 24 62 50

I have the following view (check out the sorting).
Select * from tbltest order by wins desc, diff, pct desc, pts desc

The diff column needs ascending sort because lower is better for that sort all other need desc sort because higher is better.

I need to rank these with a numeric number based on the way they are sorted: 1 2 3 4

If they tie, I need to duplicate the rank. 1, 2, 2, 4 etc…

if there were only two columns the ranking would look like this:
name wins diff rank
admin14 10 0 1
admin11 10 1 2
admin12 10 24 3
administrator 10 24 3


I do not know how to write the ranking code in sql off this view. I also do not know how to figure the rank for all the columns. Can you provide some feed back on what i am trying to do.

 

[jacob94]

Let me think about the ORDERBYID thing. I would rather take your advice to avoid performance issues.

If you were to redesign this, what would you do differently?

It offers 6 drop downs that drive rankings and sort order.

 

[hmckillop]

Jacob

I havent abandoned this yet, just havent had much time to look at this. I will post back later this week.
Cheers


"I'm living so far beyond my income that we may almost be said to be living apart

 

[jacob94]

Thanks, I look forward to the discussion.