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!!

S3S · Answer 1 · 2018-07-09T19:07:13.800

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 |
+-----+----+------+-----------+

+1. @NWHikerOR, if you want to see an illustration of the differences between the `row_number`, `rank`, and `dense_rank` functions, also see [this question](https://stackoverflow.com/questions/7747327/sql-rank-versus-row-number). — Joe Farrell, Jul 09 '18 at 19:06
Nice link @JoeFarrell and I was just adding some sample data too! — S3S, Jul 09 '18 at 19:07
Accounting for ties is ok, I can live with the same number in this case. However, if I use ROW_NUMBER, is there a way I can have the number start at, say, 10 and descend backwards? — NWHikerOR, Jul 09 '18 at 19:35
No @NWHikerOR unless there are only 10 rows in the partition or you use `TOP 10` to only bring back 10 rows. — S3S, Jul 09 '18 at 19:37
OK...well, I do know that there is only 9 rows that I am dealing with. — NWHikerOR, Jul 09 '18 at 19:58
Then all of the functions would be the same, and you should be good to go. Feel free to post the 10 rows if you'd like us to show you the exact code @NWHikerOR — S3S, Jul 09 '18 at 20:00

Yogesh Sharma · Accepted Answer · 2018-07-09T18:48:22.693

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

edited Jul 09 '18 at 18:48

answered Jul 09 '18 at 18:43

Yogesh Sharma

49,870
5
26
52

1

but what if they wanted to use rank because they wanted to account for ties? – S3S Jul 09 '18 at 18:44
Accounting for ties is ok, I can live with the same number in this case. However, if I use ROW_NUMBER, is there a way I can have the number start at, say, 10 and descend backwards? – NWHikerOR Jul 09 '18 at 19:34

score 1 · Answer 3 · answered Jul 09 '18 at 21:21

1

Like this:

;WITH cte AS
(
    SELECT 
        Salesperson,
        11 - RANK() OVER (ORDER BY ORDERS DESC) AS Ranking,
        TotalSales
    FROM 
        OrdersTable
)
SELECT * 
FROM cte
ORDER BY Ranking DESC

answered Jul 09 '18 at 21:21

RBarryYoung

55,398
14
96
137

score 0 · Answer 4 · answered Jul 09 '18 at 21:11

This is most likely what you're looking for:

;WITH CTE (Salesperson, Orders, TotalSales) AS (
    SELECT * FROM (
        VALUES
            (5695,270,23500), 
            (8475,310,46000), 
            (1287,105,9000), 
            (5412,475,75600)
    ) AS A (Column1, Column2, Column3)
)

SELECT Salesperson, 
    Orders, 
    TotalSales, 
    Ranking = RANK() OVER (
        ORDER BY Orders
        )
FROM CTE
ORDER BY ORDERS DESC

Output:

+-------------+--------+------------+---------+
| Salesperson | Orders | TotalSales | Ranking |
+-------------+--------+------------+---------+
|        5412 |    475 |      75600 |       4 |
|        8475 |    310 |      46000 |       3 |
|        5695 |    270 |      23500 |       2 |
|        1287 |    105 |       9000 |       1 |
+-------------+--------+------------+---------+

This would expand or contract as you needed, it is simply ordering on the base RANK and then reordered on the SELECT in descending order.

Rank the number of orders in reverse

4 Answers4