Generate a sequence based on Alphabetical order:

Hi,
i have data as below

CODE

421
BD
AA
AA
CD
KK
MM

i Want to create sequence based on Alphabetical order

CODE   seq

421   1
AA    2
AA    2
BD    3
CD    4
KK    5
MM  6


Thanks
sam2929Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
oh I didn't even notice the repeat in the results.

But that's simple enough, just change row_number to dense_rank in the first post


SELECT code, DENSE_RANK() OVER (ORDER BY code) rn
  FROM yourdata;


this way you don't need to double query your table,  it's just a sort.
0
 
sdstuberCommented:
SELECT code, ROW_NUMBER() OVER (ORDER BY code) rn
  FROM yourdata;


or

SELECT code, ROWNUM
  FROM (SELECT code
          FROM yourdata
        ORDER BY code);
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
try this :

select x.code, x.rr
from xyz y, ( select code, rownum rr
from (
select code
from xyz
group by code
order by code ) ) x
where x.code = y.code
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
If the queries given by sdstuber did not give you the exact output you are looking for, then my query should give the output you are looking for.  my query will give 2 for both AA code records where as the sdstuber queries will give 2 and 3 for the AA code records.

Thanks,
0
 
awking00Commented:
Can there be lowercase letters involved?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.