Skip to Main Content
  • Questions
  • How to sort in alphabetical order and get the 2nd ranked rows

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, kanaka.

Asked: September 06, 2018 - 10:45 am UTC

Last updated: September 10, 2018 - 4:15 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

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





and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

using NTH_VALUE function

Rajeshwaran, Jeyabal, September 10, 2018 - 11:21 am UTC

demo@ORA11G> select x, nth_value(x,2) over( partition by substr(x,1,1) order by x ) r2
  2  from t
  3  /

X          R2
---------- ----------
ADAMS
ALLEN      ALLEN
BLAKE
CLARK
FORD
JAMES
JONES      JONES
KING
MARTIN
MILLER     MILLER
SCOTT
SMITH      SMITH
TUNNER
WARD

14 rows selected.

demo@ORA11G> select r2
  2  from (
  3  select x, nth_value(x,2) over( partition by substr(x,1,1) order by x ) r2
  4  from t
  5      )
  6  where r2 is not null
  7  /

R2
----------
ALLEN
JONES
MILLER
SMITH

demo@ORA11G>

Chris Saxon
September 10, 2018 - 4:15 pm UTC

Yes, that's another way.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.