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

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

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

Thanks in advance.

Answer

Use dense_rank():

dense_rank() over (order by TECH_SPEC) as TECH_SPEC_NO

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

Advertisement

Answer