CODEWITHSUNDEEP
sqlsql-serveroracle-databasehiveql
Akash Jain
Akash Jain

Reputation: 55

Sql Query / Calculate rank for values based on date and id

For each date and id we have to give the rank to values in decreasing order.

Below is the Input Table.

>input data
+------------+----+-------+
|    date    | id | value |
+------------+----+-------+
| 01-01-2018 | A  |    20 |
| 01-01-2018 | A  |    50 |
| 01-01-2018 | C  |    40 |
| 01-01-2018 | B  |    40 |
| 02-01-2018 | A  |    30 |
| 03-01-2018 | C  |    20 |
| 03-01-2018 | C  |    40 |
| 04-01-2018 | B  |     0 |
| 04-01-2018 | B  |    40 |
| 05-01-2018 | B  |    70 |
+------------+----+-------+

Output should look like below:

>output data
+------------+----+-------+------+
|    date    | id | value | rank |
+------------+----+-------+------+
| 01-01-2018 | A  |    50 |    1 |
| 01-01-2018 | A  |    20 |    2 |
| 01-01-2018 | B  |    40 |    1 |
| 01-01-2018 | C  |    40 |    1 |
| 02-01-2018 | A  |    30 |    1 |
| 03-01-2018 | C  |    40 |    1 |
| 03-01-2018 | C  |    20 |    2 |
| 04-01-2018 | B  |    40 |    1 |
| 04-01-2018 | B  |     0 |    2 |
| 05-01-2018 | B  |    70 |    1 |
+------------+----+-------+------+

Upvotes: 0

Views: 873

Answers (1)

Nick
Nick

Reputation: 147146

You can use RANK(), partitioning on date and id and ordering by value descending:

SELECT *,
       RANK() OVER (PARTITION BY date, id ORDER BY value DESC) AS ranking
FROM data

Output:

date        id  value   ranking
01-01-2018  A   50      1
01-01-2018  A   20      2
01-01-2018  B   40      1
01-01-2018  C   40      1
02-01-2018  A   30      1
03-01-2018  C   40      1
03-01-2018  C   20      2
04-01-2018  B   40      1
04-01-2018  B   0       2
05-01-2018  B   70      1

Demo on SQLFiddle

This query will run on all the DBMS you have tagged your question with.

Upvotes: 3

Related Questions