CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
user2474598
user2474598

Reputation: 15

Ranking using multiple columns

I really need your help regarding this scenario: I have this table (without the 'Rank' column):

+------------+---------+---------+-------+------+--------+------------+------+
| Profile ID | Email   | lname   | fname | Type | Salary | Created    | Rank |
+------------+---------+---------+-------+------+--------+------------+------+
| 1          | [email protected] | Templar | Simon | A    | 200    | 01.01.2020 | 2    |
+------------+---------+---------+-------+------+--------+------------+------+
| 2          | [email protected] | Madison | James | B    | 100    | 01.01.2020 | 1    |
+------------+---------+---------+-------+------+--------+------------+------+
| 3          | [email protected] | Adams   | Dan   | C    | 300    | 02.01.2020 | 2    |
+------------+---------+---------+-------+------+--------+------------+------+
| 4          | [email protected] | Adams   | Emily | A    | 200    | 04.01.2020 | 2    |
+------------+---------+---------+-------+------+--------+------------+------+
| 5          | [email protected] | Adams   | Kim   | C    | 300    | 05.01.2020 | 1    |
+------------+---------+---------+-------+------+--------+------------+------+

Where I would like to rank Profiles based on the duplicate email using the following Criterion:

How can I do that using SQL Ranking?

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You want to use rank(). I think the logic is something like this:

select t.*,
       rank() over (partition by email
                    order by (case when type = 'B' then 1 else 2  end),
                             salary desc
                   ) as ranking                          
from t;

It is quite unclear what you mean by lname being the same. Same as what? But I don't think that is necessary for your logic.

Upvotes: 1

Related Questions