Rank the number of orders in reverse

Question

First, thanks for looking. I don't post a lot of questions as I can usually find an answer but this one is making me wonder if I am doing something wrong.

SQL Server 2008 (although this server will be upgraded to 2016 soon).

Don't know if this can even be done, so that's why I am asking. I am trying to rank orders and assign points to the person with the most orders. Highest person gets 10 pts, next is 9 pts, etc. etc.

Here is what I have

Salesperson       Orders     TotalSales
---------------------------------------
5695              270        23500
8475              310        46000
1287              105         9000
5412              475        75600

What I would like to see is this

Salesperson       Orders     TotalSales     Ranking
---------------------------------------------------
5412              475        75600             10
8475              310        46000              9
5695              270        23500              8
1287              105         9000              7

and so on and so on...

I'm trying the following, but its all I know about RANK

SELECT 
    Salesperson,
    RANK() OVER (PARTITION BY Orders ORDER BY ORDERS DESC),
    TotalSales
FROM 
    OrdersTable

Any help is greatly appreciated!!

Answer 1

Remove PARTITION BY Orders and DESC from the ORDER BY and you should be good.

SELECT Salesperson
       , RANK()OVER (ORDER BY ORDERS)
       , TotalSales
FROM OrdersTable

The difference in this and row_number (which Yogesh used in his answer) is if you have two values which are the same, they will be given the same value since their ranking will be the same. If you want no gaps in your numbers with ranking, then you'll want to use dense_rank

Here is a comparison of the three when there are duplicate values in the column.

declare @table table (val int)
insert into @table
values
(1),(1),(2),(3),(4),(4),(5)

select
    val
    ,RN = row_number() over (order by val)
    ,[Rank] = rank() over (order by val)
    ,[DenseRank] = dense_rank() over (order by val)
from @table

RESULTS

+-----+----+------+-----------+
| val | RN | Rank | DenseRank |
+-----+----+------+-----------+
|   1 |  1 |    1 |         1 |
|   1 |  2 |    1 |         1 |
|   2 |  3 |    3 |         2 |
|   3 |  4 |    4 |         3 |
|   4 |  5 |    5 |         4 |
|   4 |  6 |    5 |         4 |
|   5 |  7 |    7 |         5 |
+-----+----+------+-----------+

When the values are unique, you'd get the same results across the functions.

declare @tableUnique table (val int)
insert into @tableUnique
values
(1),(2),(3)
select
    val
    ,RN = row_number() over (order by val)
    ,[Rank] = rank() over (order by val)
    ,[DenseRank] = dense_rank() over (order by val)
from @tableUnique

RESULTS

+-----+----+------+-----------+
| val | RN | Rank | DenseRank |
+-----+----+------+-----------+
|   1 |  1 |    1 |         1 |
|   2 |  2 |    2 |         2 |
|   3 |  3 |    3 |         3 |
+-----+----+------+-----------+

Answer 2

Use row_number() function :

select *
from (select *, row_number() over (order by Orders) Ranking
      from OrdersTable
     ) t
order by Ranking desc;

If you have same Orders, then you can use dense_rank instead