Oracle SQL – Assign number to distinct tech_spec

In Oracle SQL, I’m trying to assign a unique number to each distinct tech_spec.

select b.job, c.spec_no, cast(a.part_id as varchar(10)) as part_id
from o_part_chg a, o_job b, unit_dept_comp_main c, parts d, o_wo e
where a.job_id = b.job_id
and b.wo_no = e.wo_no
and e.unit_id = c.unit_id
and a.part_id = d.part_id
and b.wo_no not in ( select a.wo_no from o_job a, o_part_chg b where a.job_id = b.job_id and b.qty < 0)
and e.completed_dt is not null
and e.completed_dt > sysdate - 2
and c.spec_no is not null
--and a.part_id = '620613'
order by c.spec_no asc

​x
 
select b.job, c.spec_no, cast(a.part_id as varchar(10)) as part_idfrom o_part_chg a, o_job b, unit_dept_comp_main c, parts d, o_wo ewhere a.job_id = b.job_idand b.wo_no = e.wo_noand e.unit_id = c.unit_idand a.part_id = d.part_idand b.wo_no not in ( select a.wo_no from o_job a, o_part_chg b where a.job_id = b.job_id and b.qty < 0)and e.completed_dt is not nulland e.completed_dt > sysdate - 2and c.spec_no is not null--and a.part_id = '620613'order by c.spec_no asc​

Output:

JOB         TECH_SPEC       PART_ID
24-6M-006   1213INT7600     605132
24-02-005   1213INT7600     587018
39-44-013   1213INT7600JJ   614037
06-27-000   1FAHP2M8D       588543
39-34-014   3512MACKGU713   598327
39-65-007   3512MACKGU713   591091
39-65-007   3512MACKGU713   591083
39-42-002   3512MACKGU713   627687
21-6Q-015   3512MACKGU713   594207
21-6Q-015   3512MACKGU713   588007
21-6Q-015   3512MACKGU713   64573
21-6Q-015   3512MACKGU713   592554
21-6Q-015   3512MACKGU713   589973
21-6Q-015   3512MACKGU713   589700
21-6Q-015   3512MACKGU713   441442
39-65-006   3512MACKGU713   588517
39-65-006   3512MACKGU713   594771

 
JOB         TECH_SPEC       PART_ID24-6M-006   1213INT7600     60513224-02-005   1213INT7600     58701839-44-013   1213INT7600JJ   61403706-27-000   1FAHP2M8D       58854339-34-014   3512MACKGU713   59832739-65-007   3512MACKGU713   59109139-65-007   3512MACKGU713   59108339-42-002   3512MACKGU713   62768721-6Q-015   3512MACKGU713   59420721-6Q-015   3512MACKGU713   58800721-6Q-015   3512MACKGU713   6457321-6Q-015   3512MACKGU713   59255421-6Q-015   3512MACKGU713   58997321-6Q-015   3512MACKGU713   58970021-6Q-015   3512MACKGU713   44144239-65-006   3512MACKGU713   58851739-65-006   3512MACKGU713   594771​

I’m trying to get assign each distinct tech_spec a number. I have been searching for a way to do this but can’t come up with anything.

Output desired:

JOB         TECH_SPEC       PART_ID  TECH_SPEC_NO
24-6M-006   1213INT7600     605132   1
24-02-005   1213INT7600     587018   1
39-44-013   1213INT7600JJ   614037   2
06-27-000   1FAHP2M8D       588543   3
39-34-014   3512MACKGU713   598327   4
39-65-007   3512MACKGU713   591091   4
39-65-007   3512MACKGU713   591083   4
39-42-002   3512MACKGU713   627687   4
21-6Q-015   3512MACKGU713   594207   4
21-6Q-015   3512MACKGU713   588007   4
21-6Q-015   3512MACKGU713   64573    4
21-6Q-015   3512MACKGU713   592554   4
21-6Q-015   3512MACKGU713   589973   4
21-6Q-015   3512MACKGU713   589700   4
21-6Q-015   3512MACKGU713   441442   4
39-65-006   3512MACKGU713   588517   4
39-65-006   3512MACKGU713   594771   4

 
JOB         TECH_SPEC       PART_ID  TECH_SPEC_NO24-6M-006   1213INT7600     605132   124-02-005   1213INT7600     587018   139-44-013   1213INT7600JJ   614037   206-27-000   1FAHP2M8D       588543   339-34-014   3512MACKGU713   598327   439-65-007   3512MACKGU713   591091   439-65-007   3512MACKGU713   591083   439-42-002   3512MACKGU713   627687   421-6Q-015   3512MACKGU713   594207   421-6Q-015   3512MACKGU713   588007   421-6Q-015   3512MACKGU713   64573    421-6Q-015   3512MACKGU713   592554   421-6Q-015   3512MACKGU713   589973   421-6Q-015   3512MACKGU713   589700   421-6Q-015   3512MACKGU713   441442   439-65-006   3512MACKGU713   588517   439-65-006   3512MACKGU713   594771   4​

Thanks in advance.

Answer

Use dense_rank():

dense_rank() over (order by TECH_SPEC) as TECH_SPEC_NO

 
dense_rank() over (order by TECH_SPEC) as TECH_SPEC_NO​

You should also learn to use proper, explicit JOIN syntax.

Advertisement

Answer