CODEWITHSUNDEEP
sqlsql-server
Đăng Lưu Trần
Đăng Lưu Trần

Reputation: 11

SQL Server : sort ranking list

I have a table with 3 columns ordernum, username, and amount. I want to select its rows and show an additional column expected.

The rule for calculating the expected column is as follows:

I tried with RANK() and ROW_NUMBER(), but have not been able to properly apply above algorithm.

This is my table declaration:

CREATE TABLE data
(
  ordernum INT,
  username NVARCHAR(30),
  amount MONEY
);

This is my table content:

+----------+----------+------------+
| ORDERNUM | USERNAME |   AMOUNT   |
+----------+----------+------------+
|        1 | test01   | 18382.5079 |
|        1 | test02   | 10476.0000 |
|        1 | test03   |  8128.0000 |
|        1 | test04   |  6680.0000 |
|        1 | test05   |  5388.9673 |
|        1 | test06   |  5356.0000 |
|       12 | test07   |  2806.0000 |
|       12 | test08   |  2806.0000 |
|       12 | test09   |  2806.0000 |
|       14 | test10   |  2530.0000 |
|       15 | test11   |  2330.0000 |
|       16 | test12   |  2183.0000 |
|       16 | test13   |  2182.0000 |
|       17 | test14   |  2000.0000 |
|       18 | test15   |  1621.0000 |
+----------+----------+------------+

And this is my expected result:

+----------+----------+------------+----------+
| ORDERNUM | USERNAME |   AMOUNT   | EXPECTED |
+----------+----------+------------+----------+
|        1 | test01   | 18382.5079 |        1 |
|        1 | test02   | 10476.0000 |        2 |
|        1 | test03   |  8128.0000 |        3 |
|        1 | test04   |  6680.0000 |        4 |
|        1 | test05   |  5388.9673 |        5 |
|        1 | test06   |  5356.0000 |        6 |
|       12 | test07   |  2806.0000 |       12 |
|       12 | test08   |  2806.0000 |       12 |
|       12 | test09   |  2806.0000 |       12 |
|       14 | test10   |  2530.0000 |       15 |
|       15 | test11   |  2330.0000 |       16 |
|       16 | test12   |  2183.0000 |       17 |
|       16 | test13   |  2182.0000 |       18 |
|       17 | test14   |  2000.0000 |       19 |
|       18 | test15   |  1621.0000 |       20 |
+----------+----------+------------+----------+

Here is a fiddle for the problem: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=4014cb469a9ec8f57ded5a5e0e60adaf

Upvotes: 1

Views: 51

Answers (1)

zealous
zealous

Reputation: 7503

This should work.

select
    OrderNum,
    Username,
    Amount,
    RANK() over (order by OrderNum) as Expected
from yourTable

Upvotes: 1

Related Questions