all
I’ve been trying to do, with data following the structure:
╔══════════════╦═══════════╦══╗ ║ Alphabetical ║ Numerical ║ ║ ╠══════════════╬═══════════╬══╣ ║ A ║ 15 ║ ║ ║ A ║ 30 ║ ║ ║ E ║ 100 ║ ║ ║ C ║ 45 ║ ║ ║ F ║ 25 ║ ║ ║ C ║ 65 ║ ║ ║ B ║ 25 ║ ║ ║ F ║ 35 ║ ║ ║ C ║ 100 ║ ║ ║ A ║ 10 ║ ║ ║ C ║ 20 ║ ║ ║ B ║ 5 ║ ║ ║ E ║ 10 ║ ║ ║ F ║ 85 ║ ║ ║ D ║ 30 ║ ║ ║ F ║ 1 ║ ║ ╚══════════════╩═══════════╩══╝
To get the following:
╔══════════════╦══════╦═════════╗ ║ Alphabetical ║ Rank ║ Numeric ║ ╠══════════════╬══════╬═════════╣ ║ A ║ 1 ║ 30 ║ ║ A ║ 2 ║ 15 ║ ║ A ║ 3 ║ 10 ║ ║ B ║ 1 ║ 25 ║ ║ B ║ 2 ║ 5 ║ ║ C ║ 1 ║ 100 ║ ║ C ║ 2 ║ 65 ║ ║ C ║ 3 ║ 45 ║ ║ C ║ 4 ║ 20 ║ ║ D ║ 1 ║ 30 ║ ║ E ║ 1 ║ 100 ║ ║ E ║ 2 ║ 10 ║ ║ F ║ 1 ║ 85 ║ ║ F ║ 2 ║ 35 ║ ║ F ║ 3 ║ 25 ║ ║ F ║ 4 ║ 1 ║ ╚══════════════╩══════╩═════════╝
Basically, to order the alphabetical field in ascending order, the numerical field in descending order and get the order or rank by using the order used for the numerical field, grouped by the alphabetical field.
I have only achieved it if I limit it to one specific value in the Alphabetical column, by using something like:
select ordered_src.*, ROWNUM Rank from (select src.* from Source src where alphabetical = 'A' order by Numeric desc) ordered_src;
But I have no idea how to get the result shown above. Any idea? Also, is there any alternative that will work also in mysql/mssql/etc?
Thanks!
Advertisement
Answer
Use row_number():
select s.*,
row_number() over (partition by alphabetical order by numerical desc) as rank
from source s
order by alphabetical, rank;