Hi Tom,
How to do sorting the data in alphabetical order and assign the rank of them like
I have data and assign the rank(rank must be assign alphabetical order) like this.
for that, I used to order by,
A ADAMS-1
ALLEN-2
B BLAKE-1
C CLARK-1
F FORD-1
J JAMES-1
J JONES-2
K KING-1
M MARTIN-1
MILLER-2
S SCOTT-1
SMITH-2
T TUNNER-1
W WARD-1
so, this should be like all the 2nd rank employees
o/p:
ALLEN-2
JONES-2
MILLER-2
SMITH-2
isn't possible in one single select statement and with using any REG_EXP
Thanks,
Kanaka
So you want to rank names using the first letter? And only return the second-ranked row for each?
You can do the ranking by using row_number. The partition by clause splits the rows into separate ranking groups. As this is the first letter, just substr the name to get this.
Which looks like:
with rank_names_by_letter as (
select last_name,
row_number () over (
partition by substr ( last_name, 1, 1 )
order by last_name
) rn
from hr.employees
where department_id = 50
)
select *
from rank_names_by_letter
order by last_name;
LAST_NAME RN
Atkinson 1
Bell 1
Bissot 2
Bull 3
Cabrio 1
Chung 2
Davies 1
Dellinger 2
Dilly 3
Everett 1
Feeney 1
Fleaur 2
Fripp 3
Gates 1
Gee 2
Geoni 3
Grant 4
...
From here it's just a matter of adding a where clause to show the Nth for each letter.